create or replace package ac_diff is

  -- Author  : LEV ERUSALIMSKIY
  -- Created : 04/12/2011 8:56:50 AM
  -- Purpose :

  -- Public type declarations
-- procedure app_params(pi_app_id1 number, pi_app_id2 number, pout_data in out ac_utils.res_type);

 procedure  app_diff_pages(pi_app_id1 number, pi_app_id2 number, p_res_id number, pi_page_id number);

 function Create_Header(p_app_id_1 varchar2, p_app_id_2 varchar2) return number;

 function app_diff_init(pi_app_id1 number, pi_app_id2 number) return number;

 -- function cr(p_str varchar2) return varchar2;

end ac_diff;
/

create or replace package body ac_diff is
-- forward definitions
 function  app_page_parameters(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number, p_sort number) return number;
 procedure app_params_details (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_comp    (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_app_proc(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_appl_items(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_appl_lov(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);
 procedure app_pages   (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number);
 procedure app_auth_schemes(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);
 procedure app_appl_authorization(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);
 procedure app_subst_string(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_nav_bar  (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_shortcuts(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_themes   (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_bc_templates    (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_button_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_calendar_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_label_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_list_templates (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_page_templates (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_popuplov_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_region_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_report_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_lists(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);
 procedure app_lists_entries(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_list_name varchar2, p_sort number);
 procedure app_breadcrumbs  (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);                             
 procedure app_bc_entries   (p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number,
                             p_breadcrumb_id1 number, p_breadcrumb_id2 number);
 procedure app_warnings(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);                             
--

 function cr(p_str varchar2) return varchar2 is
 begin
     return replace(replace(p_str, ac_utils.G_CRLF, ac_utils.G_CR), ac_utils.G_LF, ac_utils.G_CR);
 end cr;

 procedure prep_data(p_param_1_val in out clob,
                     p_param_2_val in out clob,
                     p_c_val_1 in out clob,
                     p_c_val_2 in out clob,
                     p_v_val_1 out varchar2,
                     p_v_val_2 out varchar2
                     ) as
  begin
     p_c_val_1  := p_param_1_val;
     p_c_val_1  := p_param_2_val;
     p_v_val_1  := substr(p_c_val_1,1,4000);
     p_v_val_2  := substr(p_c_val_2,1,4000);
  end  prep_data;

 function Create_Header(p_app_id_1 varchar2, p_app_id_2 varchar2) return number is
   v_workspace_1 varchar2(500);
   v_workspace_2 varchar2(500);
   v_result_id   number;
   begin
     select app.workspace
       into v_workspace_1
     from apex_applications app
      where app.application_id = p_app_id_1;

     select app.workspace
       into v_workspace_2
     from apex_applications app
      where app.application_id = p_app_id_2;

     insert into  ac_results_header
      ( result_id,      app_id_1,   app_id_2,   workspace_1,   workspace_2)
     values
      (acc_seq.nextval, p_app_id_1, p_app_id_2, v_workspace_1, v_workspace_2  )
      returning result_id INTO v_result_id;

     return v_result_id;
 end  Create_Header;

 function APP_REC(p_result_id number, p_app_id_1 varchar2, p_app_id_2 varchar2, p_sort number) return number is
   v_parent_id      number;
   v_sort           number;
   v_result_id      number := p_result_id;
   begin
    app_apex.setMsgValue(p_result_id, 'Application parameters... ');

    insert into ac_results
     (result_id,  parent_id, param_name,  param_val_1, param_val_2, sort, is_branch,   object_type , is_src)
    values
     (p_result_id, null,     'Compare Applications', p_app_id_1,  p_app_id_2, p_sort, 'Y', ac_utils.G_APPLICATION, 'N' )
    returning id INTO v_parent_id;

    app_warnings      (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,  0);
    app_params_Details(v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,  1);
    app_auth_schemes  (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2);
    app_appl_authorization(v_result_id, v_parent_id, p_app_id_1, p_app_id_2);
    app_appl_items    (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,    4);
    app_appl_lov      (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2);
    app_comp          (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,    6);
    app_app_proc      (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,    7);
    app_subst_string  (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,    8);
    app_nav_bar       (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,    9);
    app_shortcuts     (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,   10);
    app_themes        (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,   11);
    app_templates     (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,   12);
    app_lists         (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,   13);
    app_breadcrumbs   (v_result_id,   v_parent_id, p_app_id_1,  p_app_id_2,   14);
    
    v_sort := 15;
    ac_diff_not_in.app_pages_not_in(v_result_id, v_parent_id, p_app_id_1, p_app_id_2, v_sort);
    ac_diff_not_in.app_pages_not_in(v_result_id, v_parent_id, p_app_id_2, p_app_id_1, v_sort);

    return v_parent_id;
 end  APP_REC;

 procedure app_themes(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id        number;
 v_th_cur           ac_utils.SIMP_COMP_CUR_TYP;
 begin
  app_apex.setMsgValue(p_result_id, 'Themes ...');

  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Themes' , p_sort, true, ac_utils.G_APP_THEMES );
  
  ac_diff_not_in.app_themes(p_result_id, v_parent_id,  pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_themes(p_result_id, v_parent_id,  pi_app_id2, pi_app_id1);

  OPEN v_th_cur FOR 
         with t1 as
          (select t.THEME_NUMBER, t.THEME_NAME,
                  t.u_name, t.u_value, t.src_view_name 
           from vac_app_themes_4_1 t
           where t.APPLICATION_ID = pi_app_id1),
          t2 as
          (select t.THEME_NUMBER, t.THEME_NAME,
                  t.u_name, t.u_value 
           from vac_app_themes_4_1 t
           where t.APPLICATION_ID = pi_app_id2)
          select t1.THEME_NUMBER||':'|| t1.THEME_NAME object_name, 
                 t1.u_name, 
                 t1.u_value u_value_1, t2.u_value u_value_2,
                 t1.src_view_name
          from t1, t2
          where t1.THEME_NAME = t2.THEME_NAME
--           and (nvl(t1.u_value, '*#*#*') != nvl(t2.u_value, '*#*#*'))
            and ( -- select not equal values. ! here  null = null
                   ( (t1.u_value is not null or t2.u_value is not null) and 
                      lnnvl(t1.u_value=t2.u_value)
                   )
                ) 
           and (t1.u_name = t2.u_name)
          order by t1.u_name;
            
  ac_utils.simp_comp(p_result_id, v_parent_id, ac_utils.G_APP_THEMES, v_th_cur);
   
  CLOSE v_th_cur;

 end  app_themes; 

 procedure app_params_details(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id number;
 begin
    app_apex.setMsgValue(p_result_id, 'Application Properties');
    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Application Properties', p_sort, true, ac_utils.G_APPLICATION);
 
     insert into ac_results
     (result_id,   parent_id,  param_name, param_val_1,   param_val_2, sort,
      help_text, is_branch,  is_src )
     select
      p_result_id, v_parent_id, app1.u_name, app1.u_value, app2.u_value, p_sort,
      'APEX_APPLICATIONS.'||app1.u_name, 'N', 'Y'
     from
     (
      select * from vac_application_4_1 where application_id = pi_app_id1
     ) app1
     join
     (
      select * from vac_application_4_1 where application_id = pi_app_id2
     ) app2 ON
     (
      (app1.U_NAME = app2.U_NAME) and
      ( -- select not equal values. ! here  null = null
        (    (app1.u_value is not null or app2.u_value is not null)
         and lnnvl(app1.u_value=app2.u_value)
        )
      )
     );

 end app_params_details;

 procedure app_appl_items(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
  v_parent_id_0    number;
  v_appl_cur       ac_utils.SIMP_COMP_CUR_TYP;
  begin

   v_parent_id_0 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  'Appl Items', p_sort, true, ac_utils.G_APPLICATION_ITEM);

   ac_diff_not_in.appl_items_not_in(p_result_id, v_parent_id_0, pi_app_id1, pi_app_id2);
   ac_diff_not_in.appl_items_not_in(p_result_id, v_parent_id_0, pi_app_id2, pi_app_id1);

   OPEN v_appl_cur FOR
                with p1 as
                ( select ai.item_name,  ai.U_NAME, ai.U_VALUE,
                         ai.src_view_name
                  from vac_application_item_4_1 ai
                  where ai.application_id = pi_app_id1
                ),
                 p2 as
                ( select ai.item_name,  ai.U_NAME , ai.U_VALUE
                  from vac_application_item_4_1 ai
                  where ai.application_id = pi_app_id2
                )
                select
                 p1.item_name object_name, 
                 p1.U_NAME, 
                 p1.U_VALUE U_value_1, p2.u_value U_value_2,
                 p1.src_view_name
                from p1 join p2
                     ON
                     (    (p1.item_name = p2.item_name)
                      and (p1.u_name = p2.u_name)
                      and ((p1.u_value is not null or p2.u_value is not null)
                            and lnnvl(p1.u_value=p2.u_value)
                          )
                     )
                 order by p1.item_name
   ;

  ac_utils.simp_comp(p_result_id, v_parent_id_0, ac_utils.G_APPLICATION_ITEM, v_appl_cur);

  CLOSE v_appl_cur; 
 end app_appl_items;

 procedure app_appl_lov_entries(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_lov_name varchar2) is
 v_parent_id    number;
 v_lov_entr_cur     ac_utils.SIMP_COMP_CUR_TYP;
 begin
    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  'LOV Entries', 1, true, ac_utils.G_LOV_ENTRIES );

    ac_diff_not_in.app_lov_entr(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_lov_name);
    ac_diff_not_in.app_lov_entr(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, p_lov_name);

    OPEN v_lov_entr_cur FOR
        with l1 as
         (select 
            display_sequence, u_name, u_value, src_view_name
           from vac_lov_entries_4_1 lov_e
          where lov_e.application_id = pi_app_id1
            and lov_e.list_of_values_name = p_lov_name),
          l2 as
         (select 
            display_sequence, u_name, u_value  
          from vac_lov_entries_4_1 lov_e
          where lov_e.application_id = pi_app_id2
            and lov_e.list_of_values_name = p_lov_name)
        select
            'Displ. seq:' || l1.display_sequence object_name,
            l1.u_name, 
            l1.u_value  u_value_1,
            l2.u_value  u_value_2,
            l1.src_view_name
        from l1, l2
        where (l1.u_name           = l2.u_name)
         and  (l1.display_sequence = l2.display_sequence)
--         and  (nvl(l1.u_value,'*#*#*') != nvl(l2.u_value,'*#*#*'))
          and ( -- select not equal values. ! here  null = null
               ( (l1.u_value is not null or l2.u_value is not null) and 
                  lnnvl(l1.u_value=l2.u_value)
               )
              ) 
         order by l1.display_sequence
       ;

    ac_utils.simp_comp(p_result_id, v_parent_id, ac_utils.G_LOV_ENTRIES, v_lov_entr_cur);

    CLOSE v_lov_entr_cur; 

 end app_appl_lov_entries;

 procedure app_appl_lov(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id_0    number;
 v_parent_id_1    number;
 v_parent_id_2    number;
 v_curr_lov_name  varchar2(100) := '##?###';
 begin
    v_parent_id_0 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  'LOVS', 1, true, ac_utils.G_LOVS);

    ac_diff_not_in.app_appl_lov_not_in(p_result_id, v_parent_id_0, pi_app_id1, pi_app_id2);
    ac_diff_not_in.app_appl_lov_not_in(p_result_id, v_parent_id_0, pi_app_id2, pi_app_id1);

    for r in 
    ( with p1 as
      ( select lv.list_of_values_name, lv.lov_id, lv.referenced_lov_id,  lv.U_NAME, lv.U_VALUE
        from vac_lov_4_1 lv
        where lv.application_id = pi_app_id1
      ),
       p2 as
      ( select lv.list_of_values_name, lv.lov_id, lv.referenced_lov_id,  lv.U_NAME, lv.U_VALUE
        from vac_lov_4_1 lv
        where lv.application_id = pi_app_id2
      )
      select
       p1.list_of_values_name, p1.U_NAME, p1.U_VALUE U_value_1, p2.u_value  U_value_2 --, p_sort
      from p1 join p2
           ON
           (    (p1.list_of_values_name = p2.list_of_values_name)
            and (p1.u_name = p2.u_name)
--            and (nvl(p1.u_value,'#$#$#$#') != nvl(p2.u_value,'#$#$#$#'))
            and ( -- select not equal values. ! here  null = null
                   ( (p1.u_value is not null or p2.u_value is not null) and 
                      lnnvl(p1.u_value=p2.u_value)
                   )
                 ) 
           )
       order by p1.list_of_values_name
    ) loop
       if (r.list_of_values_name != v_curr_lov_name) then
         v_parent_id_1      := ac_utils.ins_result_msg(p_result_id,  v_parent_id_0, 'LOV: '||r.list_of_values_name, 1 , true, ac_utils.G_LOVS);
         v_curr_lov_name    := r.list_of_values_name;
         app_appl_lov_entries(p_result_id, v_parent_id_1, pi_app_id1, pi_app_id2, r.list_of_values_name );
       end if;

       insert into ac_results
       (result_id,   parent_id,     param_name, param_val_1, param_val_2 , sort,
        help_text, is_branch, is_src)
       values
       (p_result_id, v_parent_id_1, r.U_NAME,  r.U_value_1, r.U_value_2,     2,
        'APEX_APPLICATION_LOVS.'||r.U_NAME,'N','N'   )
       returning  id into v_parent_id_2;

    end loop;
 end app_appl_lov;

 procedure app_page_item_parameters(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number, p_sort number) as
   v_parent_id      number;
   v_parent_id_0    number;
   v_curr_item_name varchar2(100);
   begin
     v_parent_id_0 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Items', 1, true, ac_utils.G_PAGE_ITEMS);

     for r in (
      with p1 as
      ( select pi1.page_id, pi1.item_name,  pi1.U_NAME , pi1.U_VALUE
        from vac_page_item_4_1 pi1
        where pi1.application_id = pi_app_id1
         and pi1.page_id = p_page_id
      ),
       p2 as
      ( select pi1.page_id, pi1.item_name,  pi1.U_NAME , pi1.U_VALUE
        from vac_page_item_4_1 pi1
        where pi1.application_id = pi_app_id2
         and pi1.page_id = p_page_id
      )
      select
       p_result_id, v_parent_id, p1.item_name, p1.U_NAME, p1.U_VALUE U_value_1, p2.u_value  U_value_2, p_sort
      from p1 join p2
           ON
           (    (p1.item_name = p2.item_name)
            and (p1.u_name = p2.u_name)
--            and (nvl(p1.u_value,'#$#$#$#') != nvl(p2.u_value,'#$#$#$#'))
            and ( -- select not equal values. ! here  null = null
                   ( (p1.u_value is not null or p2.u_value is not null) and 
                      lnnvl(p1.u_value=p2.u_value)
                   )
                 ) 
           )
       order by p1.item_name
       ) loop

        if (nvl(v_curr_item_name,'#$#$#$#') !=  r.item_name) then
         v_parent_id      := ac_utils.ins_result_msg(p_result_id,  v_parent_id_0, 'Item: '||r.item_name, 1 , true, ac_utils.G_PAGE_ITEMS);
         v_curr_item_name := r.item_name;
        end if;
        insert into ac_results
        (result_id,   parent_id,   param_name, param_val_1, param_val_2 , sort,
         help_text, is_branch, is_src)
        values
        (p_result_id, v_parent_id, r.U_NAME,  r.U_value_1, r.U_value_2,     1,
         'APEX_APPLICATION_PAGE_ITEMS.'||r.u_name, 'N', 'N');

      end loop;
   end app_page_item_parameters;

 function app_page_items(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number, p_sort number) return number  is
 v_sort      number := p_sort;
 begin
    ac_diff_not_in.app_page_items_not_in   (p_result_id, pi_parent_id, pi_app_id1, pi_app_id2, p_page_id);
    ac_diff_not_in.app_page_items_not_in   (p_result_id, pi_parent_id, pi_app_id2, pi_app_id1, p_page_id);
    app_page_item_parameters(p_result_id, pi_parent_id, pi_app_id1, pi_app_id2, p_page_id, v_sort);

    return v_sort;
 end  app_page_items;

 function app_page_parameters(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number, p_sort number) return number is
 v_sort number := p_sort;
 v_src_1          clob;
 v_src_2          clob;
 v_is_src         varchar2(1);
 v_value_1        varchar2(4000);
 v_value_2        varchar2(4000);
 v_param_name     varchar2(4000);
 begin
    for r in (
                with app_page1 as (
               select application_id, page_id, u_name, u_value,
                      help_text_sr,   page_html_header_sr,
                      javascript_code_onload_sr,
                      src_view_name
                from vac_page_4_1 ap1
                where ap1.application_id = pi_app_id1
                  and ap1.page_id = p_page_id
                ),
                 app_page2 as (
               select application_id, page_id, u_name, u_value,
                      help_text_sr,   page_html_header_sr,
                      javascript_code_onload_sr
                from vac_page_4_1 ap1
                where ap1.application_id = pi_app_id2
                  and ap1.page_id = p_page_id
                )
                select p1.page_id,            p1.u_name,
                       p1.u_value u_value_1,  p2.u_value u_value_2,
                       p1.help_text_sr        help_text_sr_1,
                       p1.page_html_header_sr page_html_header_sr_1,
                       p1.javascript_code_onload_sr javascript_code_onload_sr_1,
                       p2.help_text_sr        help_text_sr_2,
                       p2.page_html_header_sr page_html_header_sr_2,
                       p2.javascript_code_onload_sr javascript_code_onload_sr_2,
                       p1.src_view_name
                from app_page1 p1 join app_page2 p2
                 ON (
                       (p1.u_name = p2.u_name)
--                   and (nvl(p1.u_value,'#$#$#$#') != nvl(p2.u_value,'#$#$#$#'))
                    and ( -- select not equal values. ! here  null = null
                           ( (p1.u_value is not null or p2.u_value is not null) and 
                              lnnvl(p1.u_value=p2.u_value)
                           )
                         ) 
                   )
                 order by p1.u_name
             ) LOOP
              v_src_1 := null;
              v_src_2 := null;
              if upper(r.u_name) = 'HELP_TEXT_HS' then
                 prep_data(
                   r.help_text_sr_1, r.help_text_sr_2,
                   v_src_1,   v_src_2,
                   v_value_1, v_value_2);
                 v_param_name := 'HELP_TEXT';
                 v_is_src  := 'Y';
              elsif upper(r.u_name) = 'PAGE_HTML_HEADER_HS' then
                 prep_data(
                   r.page_html_header_sr_1, r.page_html_header_sr_2,
                   v_src_1,   v_src_2,
                   v_value_1, v_value_2);
                 v_param_name := 'PAGE_HTML_HEADER';
                 v_is_src  := 'Y';
              elsif upper(r.u_name) = 'JAVASCRIPT_CODE_ONLOAD_HS' then
                 prep_data(
                   r.javascript_code_onload_sr_1, r.javascript_code_onload_sr_2,
                   v_src_1,   v_src_2,
                   v_value_1, v_value_2);
                 v_param_name := 'JAVASCRIPT_CODE_ONLOAD';
                 v_is_src  := 'Y';
               else
                 v_is_src  := 'N';
                 v_value_1 := r.U_value_1;
                 v_value_2 := r.U_value_2;
                 v_param_name := upper(r.u_name);
               end if;

       insert into ac_results
          (result_id,   parent_id,   param_name, param_val_1, param_val_2, object_type,
           help_text,    sort,
           src_val_1,   src_val_2,   is_branch,  is_src)
       values
          (p_result_id, pi_parent_id, v_param_name,   v_value_1,   v_value_2, ac_utils.G_PAGE_ITEMS,
           'APEX_APPLICATION_PAGES.'||r.u_name,       v_sort,
           v_src_1, v_src_2,         'N', 'N');
       v_sort := v_sort + 1;
     end loop;

     return v_sort;
 end  app_page_parameters;

 function is_report(pi_app_id number, pi_page_id number, pi_region_id number) return boolean is
   v_res boolean;
   v_tmp       number;
   begin
     begin
     select r1.region_id into v_tmp
                  from APEX_APPLICATION_PAGE_RPT r1
                  where r1.application_id = pi_app_id
                    and r1.page_id   = pi_page_id
                    and r1.region_id = pi_region_id;
       v_res := true;
     exception when NO_DATA_FOUND then
       v_res := false;
     end;
     return v_res;
   end is_report;

 procedure app_report_cols(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, pi_region_id1 number, pi_region_id2 number) is
   v_parent_id      number;
   v_parent_id_cols number;
   v_column_alias   varchar2(100) := '*****';
   v_title          varchar2(100) := 'Report columns';

   v_src_1         clob;
   v_src_2         clob;
   v_is_src        varchar2(1) := 'N';
   v_value_1       varchar2(4000);
   v_value_2       varchar2(4000);
   v_param_name    varchar2(4000);

   begin
    -- app_apex.setMsgValue(p_result_id, 'Report columns');

     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_title, 3, true, ac_utils.G_REPORT_COLUMN);
     ac_diff_not_in.app_report_cols_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id, pi_region_id1, pi_region_id2);
     ac_diff_not_in.app_report_cols_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id, pi_region_id2, pi_region_id1);

     for r in ( with app_r1 as (
                                select application_id, page_id, region_name, region_id, r1.column_alias,
                                       primary_key_column_source_sr, u_name, u_value
                                from vac_page_rpt_cols_4_1 r1
                                where r1.application_id = pi_app_id1
                                  and r1.page_id   = pi_page_id
                                  and r1.region_id = pi_region_id1
                                ),
                               app_r2 as (
                                select application_id, page_id, region_name, region_id, r2.column_alias,
                                       primary_key_column_source_sr, u_name, u_value,
                                       r2.src_view_name
                                  from vac_page_rpt_cols_4_1 r2
                                 where r2.application_id = pi_app_id2
                                   and r2.page_id   = pi_page_id
                                   and r2.region_id = pi_region_id2
                                )
                                  select r1.page_id,              r1.column_alias, r1.u_name,
                                         r1.region_id region_id1, r2.region_id region_id2,
                                         r1.u_value u_value_1,    r2.u_value u_value_2,
                                         r1.primary_key_column_source_sr primary_key_column_source_sr_1,
                                         r2.primary_key_column_source_sr primary_key_column_source_sr_2,
                                         r2.src_view_name
                                  from app_r1 r1 join app_r2 r2
                                   ON (
                                         (r1.u_name  = r2.u_name)
                                     and (r1.column_alias = r2.column_alias)
--                                     and (nvl(r1.u_value,'#$#$#$#') != nvl(r2.u_value,'#$#$#$#'))
                                     and ( -- select not equal values. ! here  null = null
                                             ( (r1.u_value is not null or r2.u_value is not null) and 
                                                lnnvl(r1.u_value=r2.u_value)
                                             )
                                          ) 
                                     )
                                   order by r1.column_alias
                  ) loop
             if r.column_alias != v_column_alias then
                 v_parent_id_cols := ac_utils.ins_result_msg(p_result_id,  v_parent_id, 'Column:'||r.column_alias, 3, true, ac_utils.G_REPORT_COLUMN);
                 v_column_alias   := r.column_alias;
             end if;

             if upper(r.u_name) = 'PRIMARY_KEY_COLUMN_SOURCE_HS' then
               v_src_1  := r.primary_key_column_source_sr_1;
               v_src_2  := r.primary_key_column_source_sr_2;
               v_value_1:= substr(v_src_1,1,4000);
               v_value_2:= substr(v_src_2,1,4000);
               v_param_name := 'PRIMARY_KEY_COLUMN_SOURCE';
               v_is_src := 'Y';
             else
               v_src_1   := null;
               v_src_2   := null;
               v_is_src  := 'N';
               v_value_1 := r.U_value_1;
               v_value_2 := r.U_value_2;
               v_param_name := upper(r.u_name);
             end if;

            insert into ac_results
            (result_id,   parent_id,        param_name,   param_val_1, param_val_2 , sort,
            help_text, is_branch, is_src)
            values
            (p_result_id, v_parent_id_cols, v_param_name, v_value_1,   v_value_2, 1,
             r.src_view_name || r.u_name,'N','N'   );

        end loop;
 end  app_report_cols;

 procedure app_report_print_params(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, pi_region_id1 number, pi_region_id2 number) is
 v_parent_id number;
 begin
    --   app_apex.setMsgValue(p_result_id, 'Report Print Parameters');

       v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Report Print Parameters', 1, true, ac_utils.G_REPORT_PRINT_PARAMETER);
       for r in (  with app_r1 as (
                    select application_id, page_id, region_name, region_id, u_name, u_value
                    from vac_page_rpt_4_1 r1
                    where r1.application_id = pi_app_id1
                      and r1.page_id   = pi_page_id
                      and r1.region_id = pi_region_id1
                    ),
                   app_r2 as (
                    select application_id, page_id, region_name, region_id, u_name, u_value
                      from vac_page_rpt_4_1 r2
                     where r2.application_id = pi_app_id2
                       and r2.page_id   = pi_page_id
                       and r2.region_id = pi_region_id2
                    )
                      select r1.page_id,              r1.u_name,
                             r1.region_id region_id1, r2.region_id region_id2,
                             r1.u_value u_value_1,    r2.u_value u_value_2
                      from app_r1 r1 join app_r2 r2
                       ON (
                             (r1.u_name  = r2.u_name)
--                         and (nvl(r1.u_value,'#$#$#$#') != nvl(r2.u_value,'#$#$#$#'))
                           and ( -- select not equal values. ! here  null = null
                                   ( (r1.u_value is not null or r2.u_value is not null) and 
                                      lnnvl(r1.u_value=r2.u_value)
                                   )
                               ) 
                         )
                 ) loop
            insert into ac_results
            (result_id,   parent_id,   param_name, param_val_1, param_val_2 , sort,
            help_text, is_branch, is_src)
            values
            (p_result_id, v_parent_id, r.U_NAME,  r.U_value_1, r.U_value_2, 1   ,
            'APEX_APPLICATION_PAGE_RPT.'||r.u_name,'N','N');
       end loop;
 end app_report_print_params;

 function app_page_branches(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number  is
 v_parent_id   number;
 v_parent_id_1 number;
 v_branch_sig  varchar2(100) := '******';
 v_title       varchar2(1000) := 'Branches';
 v_sort        number := p_sort + 1;
 begin
    v_parent_id   := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_title, 1, true, ac_utils.G_BRANCH);
    ac_diff_not_in.app_page_branches_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
    ac_diff_not_in.app_page_branches_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);

    for r in (with b1 as (
                    select
                     branch_point||':'||process_sequence br_sig,
                     branch_point, process_sequence,
                     u_value, u_name
                    from  vac_branch_4_1 b
                     where b.application_id = pi_app_id1
                      and  b.page_id = pi_page_id
                    ),
                     b2 as (
                    select
                     branch_point||':'|| process_sequence br_sig,
                     branch_point, process_sequence,
                     u_value, u_name
                    from  vac_branch_4_1 b
                     where b.application_id = pi_app_id2
                      and  b.page_id = pi_page_id
                    )
                    select b1.br_sig, b1.u_name, b1.u_value u_value_1,  b2.u_value u_value_2
                    from b1 join b2 ON
                    (    (b1.branch_point = b2.branch_point)
                     and (b1.process_sequence = b2.process_sequence)
                     and (b1.u_name = b2.u_name)
--                     and (nvl(b1.u_value,'#$#$#$#') != nvl(b2.u_value,'#$#$#$#'))
                     and ( -- select not equal values. ! here  null = null
                             ( (b1.u_value is not null or b2.u_value is not null) and 
                                lnnvl(b1.u_value=b2.u_value)
                             )
                          ) 
                    )
                    order by  b1.br_sig
              ) loop

        if (v_branch_sig != r.br_sig) then
            v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  v_parent_id, v_title||' '||r.br_sig, 1, true, ac_utils.G_BRANCH);
            v_branch_sig  := r.br_sig;
        end if;

        insert into ac_results
        (result_id,   parent_id,     param_name, param_val_1, param_val_2 , sort , help_text,
         is_branch, is_src)
          values
        (p_result_id, v_parent_id_1, r.U_NAME,     r.U_value_1, r.U_value_2, 1,
         'APEX_APPLICATION_PAGE_BRANCHES.'||r.u_name,
         'N', 'N' );

      end loop;

      return v_sort;
  end app_page_branches;

-- This is report print parameters
 procedure app_region_rpt(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, pi_region_id1 number, pi_region_id2 number) is
   v_parent_id     number;
   v_parent_id_tmp number;
   v_is_report_app1 boolean := is_report(pi_app_id1, pi_page_id, pi_region_id1);
   v_is_report_app2 boolean := is_report(pi_app_id2, pi_page_id, pi_region_id2);

   v_msg         varchar2(1000);
   v_title       varchar2(1000) := 'Report';
 begin
 --    app_apex.setMsgValue(p_result_id, 'Reports');
     if (v_is_report_app1 or v_is_report_app2) then
       v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, ac_utils.G_REPORT, 1, true, ac_utils.G_REPORT);
     end if;

     if v_is_report_app1 and (not v_is_report_app2) then
      v_msg           := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
      v_parent_id_tmp := ac_utils.ins_result_msg(p_result_id,  v_parent_id, v_msg, 1);
     elsif v_is_report_app2 and (not v_is_report_app1) then
      v_msg           := ac_utils.in1NotIn2(v_title, pi_app_id2, pi_app_id1);
      v_parent_id_tmp := ac_utils.ins_result_msg(p_result_id,  v_parent_id, v_msg, 1);
     elsif (v_is_report_app1 and v_is_report_app2) then
      app_report_print_params(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id, pi_region_id1, pi_region_id2);
      app_report_cols        (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id, pi_region_id1, pi_region_id2);
    end if; -- if v_is_report_app1 and (not v_is_report_app2) then
 end  app_region_rpt;

 function app_regions(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id     number;
 v_parent_id_reg number;
 v_sort          number := p_sort;
 v_src_1         clob;
 v_src_2         clob;
 v_is_src        varchar2(1);
 v_value_1       varchar2(4000);
 v_value_2       varchar2(4000);
 v_param_name    varchar2(4000);
 begin
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Regions', v_sort, true, ac_utils.G_REGION);

     ac_diff_not_in.app_regions_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
     ac_diff_not_in.app_regions_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);

     for r in (select r1.display_sequence, r1.region_id region_id1,
                      r2.region_id  region_id2
                 from  APEX_APPLICATION_PAGE_REGIONS r1
                  join APEX_APPLICATION_PAGE_REGIONS r2
                 ON (r1.display_sequence = r2.display_sequence) and (r1.page_id = r2.page_id)
                 where r1.application_id = pi_app_id1
                  and  r2.application_id = pi_app_id2
                  and  r1.page_id =  pi_page_id
              ) loop
       v_parent_id_reg := ac_utils.ins_result_msg(p_result_id,  v_parent_id, 'Region display seq:'|| r.display_sequence, 1 , true, ac_utils.G_REGION );

       for rec in (
               with app_r1 as (
               select application_id, page_id, display_sequence, region_id,
                      region_source_sr, u_name, 
                      u_value
                from vac_page_regions_4_1 r1
                where r1.application_id = pi_app_id1
                  and r1.page_id = pi_page_id
                  and r1.display_sequence = r.display_sequence
                ),
                 app_r2 as (
               select application_id, page_id, display_sequence, region_id,
                      region_source_sr, u_name,  
                      u_value
                from vac_page_regions_4_1 r2
                where r2.application_id = pi_app_id2
                  and r2.page_id = pi_page_id
                  and r2.display_sequence = r.display_sequence
                )
                select r1.page_id,              r1.u_name,
                       r1.region_id region_id1, r2.region_id region_id2,
                       r1.region_source_sr region_source_sr_1,
                       r2.region_source_sr region_source_sr_2,
                       r1.u_value u_value_1,    r2.u_value u_value_2
                from app_r1 r1 join app_r2 r2
                 ON (
                       (r1.u_name   = r2.u_name)
--                   and (nvl(r1.u_value,'#$#$#$#') != nvl(r2.u_value,'#$#$#$#'))
                   and ( -- select not equal values. ! here  null = null
                           ( (r1.u_value is not null or r2.u_value is not null) and 
                              lnnvl(r1.u_value=r2.u_value)
                           )
                       ) 
                     )
                  ) loop
             if (rec.U_NAME = 'REGION_SOURCE_HS') then
               v_src_1   := rec.REGION_SOURCE_sr_1;
               v_src_2   := rec.REGION_SOURCE_sr_2;
               v_value_1 := substr(v_src_1,1,4000);
               v_value_2 := substr(v_src_2,1,4000);
               v_is_src  := 'Y';
               v_param_name := 'REGION_SOURCE';
             else
               v_src_1   := null;
               v_src_2   := null;
               v_is_src  := 'N';
               v_value_1 := rec.U_value_1;
               v_value_2 := rec.U_value_2;
               v_param_name := upper(rec.u_name);
             end if;

          insert into ac_results
          (result_id,   parent_id,       param_name,    param_val_1, param_val_2,
           sort,     help_text, is_branch, is_src)
          values
          (p_result_id, v_parent_id_reg, v_param_name,  v_value_1,   v_value_2, 1,
           'APEX_APPLICATION_PAGE_REGIONS.'||v_param_name,'N',v_is_src );
       end loop;

       app_region_rpt(p_result_id, v_parent_id_reg, pi_app_id1, pi_app_id2, pi_page_id, r.region_id1, r.region_id2);

     end loop;

  return   v_sort;
 end app_regions;

 function app_ir_comp(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id       number;
 v_cur             SYS_REFCURSOR;
 begin
 --  app_apex.setMsgValue(p_result_id, 'Comput. in user-level report settings for an IR ...');

   v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Comput. in user-level report settings for an IR', p_sort, true, ac_utils.G_IR_COMP );
   ac_diff_not_in.app_page_ir_comp(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
   ac_diff_not_in.app_page_ir_comp(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);
   
   OPEN v_cur FOR 
     with v1 as ( select ir.COMPUTATION_COLUMN_ALIAS, ir.U_NAME, ir.U_VALUE,
                         ir.src_view_name
                  from vac_app_page_ir_comp_4_1 ir
                  where ir.APPLICATION_ID = pi_app_id1
                    and ir.PAGE_ID = pi_page_id
                ),
                 v2 as
                ( select ir.COMPUTATION_COLUMN_ALIAS, ir.U_NAME, ir.U_VALUE 
                  from vac_app_page_ir_comp_4_1 ir
                  where ir.APPLICATION_ID = pi_app_id2
                    and ir.PAGE_ID = pi_page_id
                )
                select v1.COMPUTATION_COLUMN_ALIAS t_name, v1.u_name,  
                       v1.u_value u_value_1, v2.u_value u_value_2,
                       v1.src_view_name 
                from v1, v2
                 where v1.COMPUTATION_COLUMN_ALIAS = v2.COMPUTATION_COLUMN_ALIAS
                   and v1.u_name  =  v2.u_name
                --               and nvl(sn1.u_value,'*#*#*#') != nvl(sn2.u_value,'*#*#*#')
                   and ( -- select not equal values. ! here  null = null
                        ( (v1.u_value is not null or v2.u_value is not null) and 
                            lnnvl(v1.u_value=v2.u_value)
                        )
                       ) 
                 order by v1.COMPUTATION_COLUMN_ALIAS;      
   
   ac_utils.compl_comp(p_result_id, v_parent_id, 1, ac_utils.G_IR_COMP , v_cur);     

   return p_sort;
 end  app_ir_comp;

 function app_ir_region_cols(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
   v_parent_id        number;
   v_parent_id_1      number;
   v_sort             number := p_sort;
   v_cur_column_alias varchar2(4000) := '*****';
   begin
   --  app_apex.setMsgValue(p_result_id, 'Interactive Report Columns ...');
   
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Interactive Report Columns', v_sort, true, ac_utils.G_INTERACTIVE_REPORT_COLS);

     ac_diff_not_in.app_report_ir_col_not_in(p_result_id,  v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
     ac_diff_not_in.app_report_ir_col_not_in(p_result_id,  v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);

     for r in (with rc1 as
               ( select page_id, u_name, u_value,
                        column_alias
                 from vac_app_page_ir_col_4_1 irc1
                 where irc1.application_id = pi_app_id1
                   and irc1.page_id = pi_page_id
               ),
               rc2 as
               (
                 select page_id, u_name, u_value,
                        column_alias, src_view_name
                 from vac_app_page_ir_col_4_1 irc1
                 where irc1.application_id = pi_app_id2
                   and irc1.page_id = pi_page_id
               )
               select rc1.column_alias, rc1.u_name,
                      rc1.u_value u_value_1,rc2.u_value u_value_2,
                      rc2.src_view_name
                from rc1 join rc2 ON (  (rc1.u_name = rc2.u_name)
                                    and (rc1.column_alias = rc2.column_alias)
--                                    and (nvl(rc1.u_value,'*#*#*') != nvl(rc2.u_value,'*#*#*'))
                                    and ( -- select not equal values. ! here  null = null
                                           ( (rc1.u_value is not null or rc2.u_value is not null) and 
                                              lnnvl(rc1.u_value=rc2.u_value)
                                           )
                                         ) 
                                     )
               order by rc1.column_alias
              ) loop
        if (r.column_alias !=  v_cur_column_alias) then
          v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  v_parent_id, r.column_alias, 1, true, ac_utils.G_INTERACTIVE_REPORT_COLS);
          v_cur_column_alias := r.column_alias;
        end if;

        insert into ac_results
        (result_id,   parent_id,     param_name, param_val_1, param_val_2,
           sort,     help_text, is_branch, is_src)
          values
        (p_result_id, v_parent_id_1, r.u_name,   r.u_value_1, r.u_value_2,
           1, r.src_view_name||'.'||r.column_alias,'N','N' );
     end loop;

     return p_sort + 1;
 end  app_ir_region_cols;

 function app_ir_regions(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id     number;
 v_parent_id_reg number;
 v_sort          number := p_sort;
 v_src_1         clob;
 v_src_2         clob;
 v_is_src        varchar2(1);
 v_value_1       varchar2(4000);
 v_value_2       varchar2(4000);
 v_param_name    varchar2(4000);
 begin
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Interactive Report', v_sort, true, ac_utils.G_INTERACTIVE_REPORT);

     ac_diff_not_in.app_page_report_ir_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
     ac_diff_not_in.app_page_report_ir_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);

     for r in (select r1.region_name
                 from  apex_application_page_ir r1
                  join apex_application_page_ir r2
                    ON (r1.region_name = r2.region_name)
                   and (r1.page_id = r2.page_id)
                 where r1.application_id = pi_app_id1
                  and  r2.application_id = pi_app_id2
                  and  r1.page_id =  pi_page_id
              ) loop
       v_parent_id_reg := ac_utils.ins_result_msg(p_result_id,  v_parent_id, 'IR Region:'|| r.region_name, 1,
                                                  true, ac_utils.G_INTERACTIVE_REPORT );
       for rec in (
               with app_r1 as (
               select application_id, page_id, region_name, region_id, u_name,         
                      r1.u_value, sql_query,   src_view_name
                from vac_app_page_ir_4_1 r1
                where r1.application_id = pi_app_id1
                  and r1.page_id = pi_page_id
                  and r1.region_name = r.region_name
                ),
                 app_r2 as (
               select application_id, page_id, region_name, region_id, u_name,         
                      r2.u_value, sql_query
                from vac_app_page_ir_4_1 r2
                where r2.application_id = pi_app_id2
                  and r2.page_id = pi_page_id
                  and r2.region_name = r.region_name
                )
                select r1.page_id,               r1.u_name,
                       r1.sql_query sql_query_1, r2.sql_query sql_query_2,
                       r1.u_value u_value_1,     r2.u_value u_value_2,
                       r1.src_view_name
                from app_r1 r1 join app_r2 r2
                 ON (
                       (r1.u_name  = r2.u_name)
--                   and (nvl(r1.u_value,'#$#$#$#') != nvl(r2.u_value,'#$#$#$#'))
                      and ( -- select not equal values. ! here  null = null
                             ( (r1.u_value is not null or r2.u_value is not null) and 
                                lnnvl(r1.u_value=r2.u_value)
                             )
                          ) 
                   )
                  ) loop
             if (rec.U_NAME = 'SQL_QUERY_HS') then
               v_src_1   := rec.sql_query_1;
               v_src_2   := rec.sql_query_2;
               v_value_1 := substr(v_src_1,1,4000);
               v_value_2 := substr(v_src_2,1,4000);
               v_is_src  := 'Y';
               v_param_name := 'SQL_QUERY';
             else
               v_is_src  := 'N';
               v_value_1 := rec.U_value_1;
               v_value_2 := rec.U_value_2;
               v_param_name := upper(rec.u_name);
             end if;

          insert into ac_results
          (result_id,   parent_id,       param_name,    param_val_1, param_val_2,
           sort,     help_text, is_branch, is_src)
          values
          (p_result_id, v_parent_id_reg, v_param_name,  v_value_1,   v_value_2,
           1, rec.src_view_name||'.'||v_param_name,'N','N' );
       end loop;
       v_sort := app_ir_region_cols(p_result_id, v_parent_id_reg, pi_app_id1, pi_app_id2, pi_page_id, p_sort);
     end loop;

     v_sort      := app_ir_comp  (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id, v_sort);
  return v_sort;
 end app_ir_regions;

 function app_page_buttons(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_page_buttons_cur ac_utils.SIMP_COMP_CUR_TYP;
 begin
     ac_diff_not_in.app_page_buttons_not_in(p_result_id, pi_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
     ac_diff_not_in.app_page_buttons_not_in(p_result_id, pi_parent_id, pi_app_id2, pi_app_id1, pi_page_id);

     OPEN v_page_buttons_cur FOR
              with app_b1 as (
                    select application_id, page_id, u_name, u_value, button_name
                    from vac_page_buttons_4_1 b1
                    where b1.application_id = pi_app_id1
                      and b1.page_id   = pi_page_id
                    ),
                   app_b2 as (
                    select application_id, page_id, u_name, u_value, button_name
                      from vac_page_buttons_4_1 b2
                     where b2.application_id = pi_app_id2
                       and b2.page_id   = pi_page_id
                    )
                      select 'Button: '|| ab1.button_name object_name,          
                             ab1.u_name, ab1.u_value u_value_1, ab2.u_value u_value_2,
                             ac_utils.G_BUTTON||'.'||ab1.u_name src_view_name 
                      from app_b1 ab1 join app_b2 ab2
                       ON (
                             (ab1.u_name      = ab2.u_name)
                         and (ab1.button_name = ab2.button_name)
--                         and (nvl(ab1.u_value,'#*#*#') != nvl(ab2.u_value,'#*#*#'))
                         and ( -- select not equal values. ! here  null = null
                               ( (ab1.u_value is not null or ab2.u_value is not null) and 
                                  lnnvl(ab1.u_value=ab2.u_value)
                               )
                             ) 
                            )
                   order by ab1.button_name
     ;

     ac_utils.simp_comp(p_result_id, pi_parent_id, ac_utils.G_BUTTON , v_page_buttons_cur);

     CLOSE v_page_buttons_cur;
   
     return  p_sort;
 end app_page_buttons;

 function app_page_validations(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id      number;
-- v_title          varchar2(1000) := ac_utils.G_VALIDATIONS;
 v_sort           number := p_sort + 1;
 v_page_valid_cur ac_utils.SIMP_COMP_CUR_TYP;
 begin
    v_parent_id   := ac_utils.ins_result_msg(p_result_id,   pi_parent_id,  'Validations', 1, true, ac_utils.G_VALIDATIONS);
    ac_diff_not_in.app_page_validations_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
    ac_diff_not_in.app_page_validations_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);

    OPEN v_page_valid_cur FOR 
        with v1 as (
                    select  v.validation_name, u_value, u_name, src_view_name
                    from  vac_val_4_1 v
                     where v.application_id = pi_app_id1
                      and  v.page_id = pi_page_id
                    ),
             v2 as (
                    select v.validation_name, u_value, u_name
                    from  vac_val_4_1 v
                     where v.application_id = pi_app_id2
                      and  v.page_id = pi_page_id
                    )
                    select v1.validation_name,   v1.u_name,
                           v1.u_value u_value_1, v2.u_value u_value_2,
                           v1.src_view_name
                    from v1 join v2 ON
                    (    (v1.validation_name = v2.validation_name)
                     and (v1.u_name  =  v2.u_name)
--                     and (nvl(v1.u_value,'#$#$#$#') != nvl(v2.u_value,'#$#$#$#'))
                      and ( -- select not equal values. ! here  null = null
                               ( (v1.u_value is not null or v2.u_value is not null) and 
                                  lnnvl(v1.u_value=v2.u_value)
                               )
                             ) 
                     )
                    order by  v1.validation_name
    ;

    ac_utils.simp_comp(p_result_id, v_parent_id, ac_utils.G_VALIDATIONS, v_page_valid_cur);

    CLOSE v_page_valid_cur;

    return v_sort;
 end  app_page_validations;

 procedure app_appl_authorization(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id      number;
 v_appl_auth_cur  ac_utils.SIMP_COMP_CUR_TYP;
 begin
    app_apex.setMsgValue(p_result_id, 'Authorization');

    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  'Authorization', 1, true, ac_utils.G_AUTHORIZATION);
    ac_diff_not_in.app_appl_authorization_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
    ac_diff_not_in.app_appl_authorization_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);

    OPEN v_appl_auth_cur FOR 
               with auth1 as
               (select authorization_scheme_name, src_view_name, u_name, u_value
                from vac_application_authoriz_4_1 auth1
                where auth1.application_id = pi_app_id1
               ),
              auth2 as
               (select authorization_scheme_name,  u_name, u_value
                from vac_application_authoriz_4_1 auth1
                where auth1.application_id = pi_app_id2
               )
              select auth1.authorization_scheme_name object_name, 
                     auth1.u_name, 
                     auth1.u_value U_value_1, auth2.u_value U_value_2,
                     auth1.src_view_name
              from auth1 join auth2 ON (auth1.u_name = auth2.u_name) and
                (auth1.authorization_scheme_name = auth2.authorization_scheme_name) and
                ( -- select not equal values. ! here  null = null
--                 (nvl(auth1.u_value,'*#*#*#') != nvl(auth2.u_value,'*#*#*#'))
                    ( -- select not equal values. ! here  null = null
                       ( (auth1.u_value is not null or auth2.u_value is not null) and 
                          lnnvl(auth1.u_value=auth2.u_value)
                       )
                     ) 
                 )
              order by auth1.authorization_scheme_name
    ;

    ac_utils.simp_comp(p_result_id, v_parent_id, ac_utils.G_AUTHORIZATION, v_appl_auth_cur);

    CLOSE v_appl_auth_cur;  

 end app_appl_authorization;

 procedure app_auth_schemes(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id    number;
 v_title          varchar2(1000) := ac_utils.G_AUTH_SCHEMES;
 v_auth_cur       SYS_REFCURSOR;
 
 begin
  app_apex.setMsgValue(p_result_id, 'Auth schemes');
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  v_title, 1, true, ac_utils.G_AUTH_SCHEMES);
  ac_diff_not_in.app_auth_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_auth_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);
  
  OPEN v_auth_cur FOR  
      with au1 as
        (select authentication_scheme_name, src_view_name,
               plsql_code_sr,  u_name, u_value
         from  VAC_APPLICATION_AUTH_4_1
         where application_id = pi_app_id1
        ),
         au2 as
        (select authentication_scheme_name, 
                src_view_name, plsql_code_sr,
                u_name, u_value
         from   VAC_APPLICATION_AUTH_4_1
         where application_id = pi_app_id2
        )
        select au1.authentication_scheme_name  t_name,
               au1.u_name,
               au1.u_value u_value_1,
               au2.u_value u_value_2,
       --       au1.plsql_code_sr plsql_code_sr_1,
       --       au2.plsql_code_sr plsql_code_sr_2,
               au1.src_view_name
         from au1, au2
         where  (au1.authentication_scheme_name = au2.authentication_scheme_name)
          and   (au1.u_name = au2.u_name)
--                and   (nvl(au1.u_value,'*#*#*#') != nvl(au2.u_value,'*#*#*#'))
          and   ( -- select not equal values. ! here  null = null
                   ( (au1.u_value is not null or au2.u_value is not null) and 
                      lnnvl(au1.u_value=au2.u_value)
                   )
                 ) 
         order by au1.authentication_scheme_name;

  ac_utils.compl_comp(p_result_id, v_parent_id, 1, ac_utils.G_AUTH_SCHEMES, v_auth_cur); 
           
 end  app_auth_schemes;

 procedure app_page_da_action(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, pi_da_action_name varchar2) is
 v_parent_id   number;
 v_da_act_cur  ac_utils.SIMP_COMP_CUR_TYP;
 begin
  app_apex.setMsgValue(p_result_id, 'Actions of DA ...');
   
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Actions of DA', 1 , true, ac_utils.G_DA_ACTIONS );

  ac_diff_not_in.app_page_da_actions(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id, pi_da_action_name );
  ac_diff_not_in.app_page_da_actions(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id, pi_da_action_name );
 
  OPEN v_da_act_cur FOR
   with daa1 as
   (
     select da.action_sequence,
            da.u_name, da.u_value, da.src_view_name
     from vac_app_page_da_action_4_1 da
     where da.application_id = pi_app_id1
       and da.page_id = pi_page_id
       and da.dynamic_action_name = pi_da_action_name
   ), daa2 as
   (
     select da.action_sequence,
            da.u_name, da.u_value
     from vac_app_page_da_action_4_1 da
     where da.application_id = pi_app_id2
       and da.page_id = pi_page_id
       and da.dynamic_action_name = pi_da_action_name
   ) 
   select 'seq:'||daa1.action_sequence t_name,
          daa1.u_name,
          daa1.u_value u_value_1, 
          daa2.u_value u_value_2,
          daa1.src_view_name
   from daa1, daa2
   where daa1.u_name = daa2.u_name
   and   daa1.action_sequence = daa2.action_sequence
   and  ( -- select not equal values. ! here  null = null
         ( (daa1.u_value is not null or daa2.u_value is not null) and 
            lnnvl(daa1.u_value=daa2.u_value)
         )
        )
   order by daa1.action_sequence;
   
   ac_utils.simp_comp(p_result_id, v_parent_id, ac_utils.G_DA_ACTIONS , v_da_act_cur);
   
   CLOSE v_da_act_cur;
 end  app_page_da_action;  

 function app_page_da(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id   number;
 v_da_name     varchar2(100) := '*#*#*#';
 v_parent_id_1 number;
-- v_parent_id_2 number;
 v_sort        number := p_sort+1;
 begin
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Dynamic Actions', p_sort, true, ac_utils.G_DYN_ACTIONS);

  ac_diff_not_in.app_page_dyn_actions_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
  ac_diff_not_in.app_page_dyn_actions_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);

  for r in(with da1 as
            (select dynamic_action_name, u_name, u_value,
                    src_view_name
             from vac_application_page_da_4_1 da
             where da.application_id = pi_app_id1
              and  da.page_id = pi_page_id
            ),
          da2 as
            ( select  dynamic_action_name, u_name, u_value
              from vac_application_page_da_4_1 da
              where da.application_id = pi_app_id2
               and  da.page_id = pi_page_id
            )
          select
           da1.dynamic_action_name, da1.u_name, da1.src_view_name,
           da1.u_value u_value_1,   da2.u_value u_value_2
          from da1 join da2
           ON   (da1.u_name = da2.u_name)
            and (da1.dynamic_action_name = da2.dynamic_action_name)
            and ( -- select not equal values. ! here  null = null
--                  (nvl(da1.u_value,'*#*#*#') != nvl(da2.u_value,'*#*#*#'))
                 ( -- select not equal values. ! here  null = null
                     ( (da1.u_value is not null or da2.u_value is not null) and 
                        lnnvl(da1.u_value=da2.u_value)
                     )
                  ) 
                )
           order by da1.dynamic_action_name
          ) loop
             if (v_da_name != r.dynamic_action_name) then
                v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  v_parent_id, r.dynamic_action_name , 1, true, ac_utils.G_DYN_ACTIONS );
                v_da_name := r.dynamic_action_name;
                app_page_da_action(p_result_id, v_parent_id_1, pi_app_id1, pi_app_id2, pi_page_id, r.dynamic_action_name);
             end if;

             insert into ac_results
              (result_id,   parent_id,     param_name, param_val_1, param_val_2 ,
               sort , help_text, object_type,
               is_branch, is_src)
             values
              (p_result_id, v_parent_id_1, r.U_NAME,   r.u_value_1 , r.u_value_2,
               3,    r.src_view_name||'.'||r.u_name, ac_utils.G_DYN_ACTIONS,
               'N', 'N'  )
--              returning id into  v_parent_id_2
              ;


    end loop;

    return v_sort;
 end  app_page_da;

 function app_page_process(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id     number;
 v_parent_id_1   number;
 v_src_1         clob;
 v_src_2         clob;
 v_is_src        varchar2(1);
 v_value_1       varchar2(4000);
 v_value_2       varchar2(4000);
 v_process_msg   varchar2(4000) := '*#*#*#';
 v_u_name        varchar2(4000) := '*#*#*#';
 begin
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Page Process', p_sort, true, ac_utils.G_PAGE_PROCESS);

  ac_diff_not_in.app_page_process(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
  ac_diff_not_in.app_page_process(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);

  for r in ( with pp1 as
             (select u_value,  u_name, process_sr,
                     src_view_name,
                     vp.process_msg
              from vac_page_process_4_1 vp
              where vp.application_id = pi_app_id1
                and vp.page_id = pi_page_id
              ),
              pp2  as
             (select u_value,  u_name, process_sr,
                    vp.process_msg
              from vac_page_process_4_1 vp
              where vp.application_id = pi_app_id2
                and vp.page_id = pi_page_id
              )
             select  pp1.u_name,
                     pp1.u_value u_value_1, pp2.u_value u_value_2,
                     pp1.process_sr process_sr_1,
                     pp2.process_sr process_sr_2,
                     pp1.src_view_name , pp1.process_msg
             from pp1 join pp2 ON (  (pp1.u_name = pp2.u_name)
                                 and (pp1.process_msg  = pp2.process_msg)
--                                 and (nvl(pp1.u_value, '*#*#*#') != nvl(pp2.u_value, '*#*#*#'))
                                  and ( -- select not equal values. ! here  null = null
                                           ( (pp1.u_value is not null or pp2.u_value is not null) and 
                                              lnnvl(pp1.u_value=pp2.u_value)
                                           )
                                       ) 
                                  )
             order by pp1.process_msg
           ) loop
             if (v_process_msg != r.process_msg) then
                v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  v_parent_id, r.process_msg, 1, true, ac_utils.G_PAGE_PROCESS );
                v_process_msg := r.process_msg;
             end if;

             if (r.U_NAME = 'PROCESS_HS') then
               v_u_name  := 'PROCESS';

               v_src_1   := r.process_sr_1;
               v_src_2   := r.process_sr_2;
               v_value_1 := substr(v_src_1,1,4000);
               v_value_2 := substr(v_src_2,1,4000);

               v_is_src  := 'Y';
             else
               v_u_name  := r.U_NAME;
               v_src_1   := null;
               v_src_2   := null;
               v_value_1 := r.U_value_1;
               v_value_2 := r.U_value_2;

               v_is_src  := 'N';
             end if;

             insert into ac_results
              (result_id,   parent_id,     param_name, param_val_1, param_val_2 ,
               sort , help_text, object_type,
               src_val_1,  src_val_2, is_branch, is_src)
             values
              (p_result_id, v_parent_id_1, v_U_NAME,     v_value_1, v_value_2,
               3,    r.src_view_name||'.'||r.u_name, null ,
               v_src_1,    v_src_2, 'N', v_is_src  );

  end loop;

  return p_sort + 1;
 end  app_page_process;

 function app_page_comp(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) return number is
 v_parent_id     number;
 v_page_comp_cur ac_utils.SIMP_COMP_CUR_TYP;
 begin
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Page Computation', p_sort, true, ac_utils.G_COMPUTATION);

  ac_diff_not_in.app_page_comp(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, pi_page_id);
  ac_diff_not_in.app_page_comp(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, pi_page_id);

  OPEN v_page_comp_cur FOR
                with pc1 as
                   ( select workspace, workspace_display_name, application_id, 
                            application_name, page_id, page_name, authorization_scheme_id, 
                            computation_id, last_updated_by, last_updated_on, item_name,
                            u_value, u_name
                     from vac_page_comp_4_1 pc1
                     where pc1.application_id = pi_app_id1
                       and pc1.page_id = pi_page_id
                    ),
                    pc2 as 
                    ( select workspace, workspace_display_name, application_id, 
                            application_name, page_id, page_name, authorization_scheme_id, 
                            computation_id, last_updated_by, last_updated_on, item_name,
                            u_value, u_name
                     from vac_page_comp_4_1 pc1
                     where pc1.application_id = pi_app_id2
                       and pc1.page_id = pi_page_id
                    )
                    select pc1.item_name object_name, pc1.u_name, 
                           pc1.u_value u_value_1, pc2.u_value u_value_2,
                           'APEX_APPLICATION_PAGE_COMP.'||pc1.u_name src_view_name
                    from pc1 join pc2 
                    ON   (pc1.item_name =  pc2.item_name)
                     and (pc1.u_name    =  pc2.u_name   )
--                     and (nvl(pc1.u_value,'*****') != nvl(pc2.u_value,'*****'))
                     and ( -- select not equal values. ! here  null = null
                               ( (pc1.u_value is not null or pc2.u_value is not null) and 
                                  lnnvl(pc1.u_value=pc2.u_value)
                               )
                         ) 
                    order by pc1.item_name
                ;

  ac_utils.simp_comp(p_result_id, v_parent_id, ac_utils.G_COMPUTATION, v_page_comp_cur);

  CLOSE v_page_comp_cur;  
    
  return p_sort+1;
 end  app_page_comp;

 procedure app_pages(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, p_sort number) is
   v_parent_id number;
   v_sort      number := p_sort;
   v_num_pages number := 1;
   begin
    app_apex.setMsgValue(p_result_id, 'Pages...');

    for r in (  select app1.page_id
                  from apex_application_pages app1 join
                       apex_application_pages app2 ON (app1.page_id = app2.page_id)
                  where app1.application_id = pi_app_id1
                    and app2.application_id = pi_app_id2
                    and ((pi_page_id is null) or
                         (app1.page_id = pi_page_id)
                        )
             ) loop
              app_apex.setMsgValue(p_result_id, '#Pages '||v_num_pages);

              v_sort      := v_sort + 1;
              v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Page id='||r.page_id, v_sort, true, ac_utils.G_PAGE );

              v_sort      := app_page_parameters (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_regions         (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_items      (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_buttons    (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_branches   (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_validations(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_da         (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_ir_regions      (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
             
              v_sort      := app_page_process    (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);
              v_sort      := app_page_comp       (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, r.page_id, v_sort);

              v_num_pages := v_num_pages + 1;

              if app_apex.isCancel(p_result_id)  then
                app_apex.setMsgValue(p_result_id,'Canceled by user...');
                v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Canceled by user...', v_sort, false, ac_utils.G_CANCELED );
                exit;
              end if;

    end loop;
 end app_pages;

 procedure app_comp(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id   number;
 --v_parent_id_1 number;
 v_app_comp_cur     ac_utils.SIMP_COMP_CUR_TYP;
 begin
    app_apex.setMsgValue(p_result_id, 'Application Computations ...');

    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Application Computations' , p_sort, true, ac_utils.G_COMPUTATION);

    ac_diff_not_in.app_comp_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
    ac_diff_not_in.app_comp_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);

    OPEN v_app_comp_cur FOR
       with app_comp1 as (
                 select computation_item, u_name, u_value,
                        ap1.src_view_name
                  from vac_application_computat_4_1  ap1
                 where ap1.application_id = pi_app_id1
                  ),
            app_comp2 as (
                 select computation_item, u_name, u_value
                  from vac_application_computat_4_1  ap1
                 where ap1.application_id = pi_app_id2
                  )
        select p1.computation_item, p1.u_name,
               p1.u_value u_value_1, p2.u_value u_value_2,
               p1.src_view_name||'.'||p1.u_name help_text
        from app_comp1 p1 join app_comp2 p2
        ON (
             (p1.computation_item = p2.computation_item)
         and (p1.u_name = p2.u_name)
--         and (nvl(p1.u_value,'#$#$#$#') != nvl(p2.u_value,'#$#$#$#'))
         and ( -- select not equal values. ! here  null = null
                 ( (p1.u_value is not null or p2.u_value is not null) and 
                    lnnvl(p1.u_value=p2.u_value)
                 )
               ) 
          )
        order by p1.computation_item;

    ac_utils.simp_comp(p_result_id, v_parent_id, ac_utils.G_COMPUTATION , v_app_comp_cur);

    CLOSE v_app_comp_cur;    
 end  app_comp;

 procedure app_app_proc(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
  v_parent_id number;
  v_parent_process_id number;
  v_process_name  varchar2(100) := '###***';
  v_num_res       number := 0;
  v_src_1         clob;
  v_src_2         clob;
  v_is_src        varchar2(1) := 'N';
  v_value_1       varchar2(4000);
  v_value_2       varchar2(4000);
  v_param_name    varchar2(4000);

 begin
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Application Processes' , p_sort, true, ac_utils.G_PROCESS);
     ac_diff_not_in.app_proc_not_in(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
     ac_diff_not_in.app_proc_not_in(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);

     for r in (
               with app_comp1 as (
                 select process_name, u_name, u_value, process_sr,
                  src_view_name
                  from vac_application_processes_4_1  ap1
                 where ap1.application_id = pi_app_id1
               ),
                    app_comp2 as (
                 select process_name, u_name, u_value, process_sr
                  from vac_application_processes_4_1  ap1
                 where ap1.application_id = pi_app_id2
               )
        select p1.process_name,
               p1.u_name,
               p1.u_value u_value_1,
               p2.u_value u_value_2,
               p1.process_sr  process_src_1,
               p2.process_sr  process_src_2,
               p1.src_view_name
        from app_comp1 p1 join app_comp2 p2
        ON (
             (p1.process_name = p2.process_name)
         and (p1.u_name = p2.u_name)
--         and (nvl(p1.u_value,'#$#$#$#') != nvl(p2.u_value,'#$#$#$#'))
         and ( -- select not equal values. ! here  null = null
              ( (p1.u_value is not null or p2.u_value is not null) and 
                lnnvl(p1.u_value=p2.u_value)
               )
             ) 
           )
           order by p1.process_name, p1.u_name
     ) loop
       if (v_process_name != r.process_name) then
         v_process_name := r.process_name;
         v_parent_process_id := ac_utils.ins_result_msg(p_result_id,  v_parent_id, 'Process:'|| r.process_name, 3, true, ac_utils.G_PROCESS);
       end if;
       if upper(r.u_name) = 'PROCESS_HS' then
         v_src_1  := r.process_src_1;
         v_src_2  := r.process_src_2;
         v_value_1:= substr(v_src_1,1,4000);
         v_value_2:= substr(v_src_2,1,4000);
         v_param_name := 'PROCESS';
         v_is_src := 'Y';
       else
         v_is_src := 'N';
         v_value_1 := r.U_value_1;
         v_value_2 := r.U_value_2;
         v_param_name := upper(r.u_name);
       end if;
       insert into ac_results
       (result_id,      parent_id,           param_name, param_val_1, param_val_2, sort,
       help_text,
       src_val_1, src_val_2, is_branch, is_src)
       values
       (p_result_id,    v_parent_process_id, v_param_name,     v_value_1, v_value_2,  v_num_res,
        r.src_view_name||'.'||v_param_name,
        v_src_1, v_src_2, 'N', v_is_src
        );
       v_num_res := v_num_res+1;
     end loop;
 end;

 procedure remove_empty_branches(p_report_id number) is
 begin
   app_apex.setMsgValue(p_report_id, 'Remove Empty Branches ...');
 
   for i in 1..100 loop -- just in case number of cycles is limited
    delete from ac_results r
    where r.result_id = p_report_id
      and r.is_branch = 'Y'
      and r.parent_id is not null
      and not exists
      ( -- branch is empty
        select 1 from ac_results r1
         where r1.result_id = p_report_id
           and r1.parent_id = r.id
      );
     exit when sql%rowcount = 0;
    end loop;
 end  remove_empty_branches;

 function getRoot(p_res_id number) return number is
 v_root_id number;
 begin
   select r.id into v_root_id
   from  ac_results r
   where r.result_id = p_res_id
    and  r.parent_id is null;

   return  v_root_id;
 end  getRoot;

 function app_diff_init(pi_app_id1 number, pi_app_id2 number) return number is
 v_res_id    number;
 v_parent_id number;
 begin
    v_res_id    := Create_Header(p_app_id_1 => pi_app_id1,p_app_id_2 => pi_app_id2);
    v_parent_id := APP_REC(v_res_id, pi_app_id1, pi_app_id2, 1);

    return v_res_id ;
 end  app_diff_init;

 procedure app_subst_string(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
  v_parent_id        number;
  v_subst_str_cur    ac_utils.SIMP_COMP_CUR_TYP;

 begin
     app_apex.setMsgValue(p_result_id, 'Substitution strings ...');

     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Substitution strings' , p_sort, true, ac_utils.G_SUBST_STRINGS);
     ac_diff_not_in.app_subst_string(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
     ac_diff_not_in.app_subst_string(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);
   
     OPEN v_subst_str_cur for 
      with as_a1 as
      ( select s.u_value, s.u_name, s.substitution_string,
               s.src_view_name
        from vac_application_substit_4_1 s
        where s.application_id = pi_app_id1
      ),
      as_a2 as
      ( select s.u_value, s.u_name, s.substitution_string 
        from vac_application_substit_4_1 s
        where s.application_id = pi_app_id2
      )
      select s1.substitution_string object_name,
             s1.u_name,
             s1.u_value u_value_1, s2.u_value u_value_2,
             s1.src_view_name
      from as_a1 s1 join as_a2 s2 
       ON  ( 
            (s1.substitution_string = s2.substitution_string)
        and (s1.u_name = s2.u_name   )
--                  and (nvl(s1.u_value,'#*#*#*') != nvl(s2.u_value,'#*#*#*'))
        and ( -- select not equal values. ! here  null = null
             ( (s1.u_value is not null or s2.u_value is not null) and 
              lnnvl(s1.u_value=s2.u_value)
             )
            ) 
           )
       order by s1.substitution_string;

     ac_utils.simp_comp(p_result_id, v_parent_id, ac_utils.G_SUBST_STRINGS , v_subst_str_cur);
        
     CLOSE v_subst_str_cur;
             
 end  app_subst_string;

 procedure app_nav_bar(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id   number;
 v_parent_id_1 number;
 v_app_nb_displ_seq  varchar2(100) := '#*#*#';
 begin
     app_apex.setMsgValue(p_result_id, 'Navigation Bar ...');
   
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Navigation Bar' , p_sort, true, ac_utils.G_NAV_BAR );
     ac_diff_not_in.app_nav_bar(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
     ac_diff_not_in.app_nav_bar(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);
     
     for r in ( with nb1 as
                (
                  select nb.display_sequence, nb.u_name, 
                         nb.u_value,
                         nb.src_view_name
                  from vac_app_nav_bar_4_1 nb
                  where nb.application_id = pi_app_id1
                ),
                nb2 as
                (
                  select nb.display_sequence, nb.u_name, 
                         nb.u_value
                  from vac_app_nav_bar_4_1 nb
                  where nb.application_id = pi_app_id2
                )
                select nb1.display_sequence,  nb1.u_name, 
                       nb1.u_value u_value_1, nb2.u_value u_value_2,
                       nb1.src_view_name
                from nb1, nb2 
                where nb1.display_sequence = nb2.display_sequence
                  and nb1.u_name  = nb2.u_name
--                  and nvl(nb1.u_value,'*#*#*') != nvl(nb2.u_value,'*#*#*')
                  and ( -- select not equal values. ! here  null = null
                        ( (nb1.u_value is not null or nb2.u_value is not null) and 
                          lnnvl(nb1.u_value=nb2.u_value)
                        )
                      ) 
                order by nb1.display_sequence
              ) loop
         if (v_app_nb_displ_seq != r.display_sequence) then
           v_parent_id_1      := ac_utils.ins_result_msg(p_result_id,  v_parent_id, 'displ. seq: '|| r.display_sequence, 3, true, ac_utils.G_NAV_BAR);
           v_app_nb_displ_seq := r.display_sequence;
         end if;
         insert into ac_results
          (result_id,      parent_id,     param_name, param_val_1, param_val_2, sort,
           help_text,      is_branch, is_src)
         values
          (p_result_id,    v_parent_id_1, r.u_name,   r.u_value_1, r.u_value_2, 1,
           r.src_view_name||'.'||r.u_name, 'N', 'N');
     end loop;
 end app_nav_bar;

 procedure app_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_templ_cur       ac_utils.SIMP_COMP_CUR_TYP;
 begin
  app_apex.setMsgValue(p_result_id, 'Templates ...');

  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Templates' , p_sort, true, ac_utils.G_APP_TEMPLATES );
  ac_diff_not_in.app_templ(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_templ(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);
  
  OPEN v_templ_cur FOR 
             with tmpl1 as (
              select 
               theme_number, template_name, src_view_name,
               u_name, u_value
              from  vac_app_templates_4_1 tmpl
              where tmpl.application_id = pi_app_id1
             ),
             tmpl2 as (
              select 
               theme_number, template_name, u_name, u_value
              from  vac_app_templates_4_1 tmpl
              where tmpl.application_id = pi_app_id2
            ) 
            select tmpl1.theme_number ||':'||tmpl1.template_name object_name,
                   tmpl1.u_name, 
                   tmpl1.u_value u_value_1, tmpl2.u_value u_value_2,
                   tmpl1.src_view_name 
            from tmpl1, tmpl2
            where tmpl1.template_name = tmpl2.template_name
              and tmpl1.theme_number  = tmpl2.theme_number
              and tmpl1.u_name        = tmpl2.u_name
--              and (nvl(tmpl1.u_value,'*#*#*') != nvl(tmpl2.u_value,'*#*#*'))
              and ( -- select not equal values. ! here  null = null
                   ( (tmpl1.u_value is not null or tmpl2.u_value is not null) and 
                      lnnvl(tmpl1.u_value=tmpl2.u_value)
                   )
                  ) 
            order by tmpl1.theme_number ||':'||tmpl1.template_name;
  
   ac_utils.simp_comp(p_result_id, v_parent_id, ac_utils.G_APP_TEMPLATES, v_templ_cur);
  
   CLOSE v_templ_cur;
  
-- templates details  
   app_bc_templates      (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_sort);  
   app_button_templates  (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_sort); 
   app_calendar_templates(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_sort); 
   app_label_templates   (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_sort); 
   app_list_templates    (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_sort); 
   app_page_templates    (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_sort); 
   app_popuplov_templates(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_sort); 
   app_region_templates  (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_sort); 
   app_report_templates  (p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_sort);
 end app_templates;

 procedure app_bc_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_bc_templ_cur    ac_utils.SIMP_COMP_CUR_TYP;
 begin
  app_apex.setMsgValue(p_result_id, 'Breadcrumb Templates... ');
 
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Breadcrumb Templates' , p_sort, true, ac_utils.G_TEMPL_BREADCRUMB );
  ac_diff_not_in.app_templ_bc(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_templ_bc(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);
 
  OPEN v_bc_templ_cur FOR 
             with tmpl1 as (
              select 
               theme_number, template_name, src_view_name,
               u_name, u_value
              from  vac_app_bc_templates_4_1 tmpl
              where tmpl.application_id = pi_app_id1
             ),
             tmpl2 as (
              select 
               theme_number, template_name, u_name, u_value
              from  vac_app_bc_templates_4_1 tmpl
              where tmpl.application_id = pi_app_id2
            ) 
            select tmpl1.theme_number ||':'||tmpl1.template_name t_name,
                   tmpl1.u_name, 
                   tmpl1.u_value u_value_1, tmpl2.u_value u_value_2,
                   tmpl1.src_view_name
            from tmpl1, tmpl2
            where tmpl1.template_name = tmpl2.template_name
              and tmpl1.theme_number  = tmpl2.theme_number
              and tmpl1.u_name        = tmpl2.u_name
--              and (nvl(tmpl1.u_value,'*#*#*') != nvl(tmpl2.u_value,'*#*#*'))
              and ( -- select not equal values. ! here  null = null
                   ( (tmpl1.u_value is not null or tmpl2.u_value is not null) and 
                      lnnvl(tmpl1.u_value=tmpl2.u_value)
                   )
                  ) 
            order by tmpl1.theme_number ||':'||tmpl1.template_name;
  
   ac_utils.simp_comp(p_result_id, v_parent_id, ac_utils.G_APP_TEMPLATES, v_bc_templ_cur);
  
   CLOSE v_bc_templ_cur;
  
 end app_bc_templates;

 procedure app_calendar_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_cur             SYS_REFCURSOR;
 begin
  app_apex.setMsgValue(p_result_id, 'Calendar Templates ...');
  
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Calendar Templates', p_sort, true, ac_utils.G_TEMPL_CALENDAR );
  ac_diff_not_in.app_TEMPL_CALENDAR(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_TEMPL_CALENDAR(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);

  OPEN v_cur FOR with tc1 as
    (SELECT  template_name, 
              theme_number, 
              u_value, 
              u_name, 
              src_view_name 
      from VAC_APP_CALENDAR_TEMPLATES_4_1 tc1
      where tc1.application_id = pi_app_id1
    ),
     tc2 as
    (SELECT  template_name, 
              theme_number, 
              u_value, 
              u_name, 
              src_view_name 
      from VAC_APP_CALENDAR_TEMPLATES_4_1 tc1
      where tc1.application_id = pi_app_id2
    ) 
    select 
     tc1.theme_number||'.'||tc1.template_name t_name,
     tc1.u_name,
     tc1.u_value u_value_1, 
     tc2.u_value u_value_2,
     tc1.src_view_name
    from tc1, tc2
     where tc1.template_name = tc2.template_name
       and tc1.theme_number  = tc2.theme_number
       and tc1.u_name        = tc2.u_name
--       and nvl(tc1.u_value,'*#*#*') != nvl(tc2.u_value,'*#*#*')
       and ( -- select not equal values. ! here  null = null
             ( (tc1.u_value is not null or tc2.u_value is not null) and 
                lnnvl(tc1.u_value=tc2.u_value)
             )
            ) 
    order by tc1.theme_number, tc1.template_name;   

  ac_utils.compl_comp(p_result_id, v_parent_id, 1, ac_utils.G_TEMPL_CALENDAR, v_cur);     
 
 end app_calendar_templates;

 procedure app_button_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_cur             SYS_REFCURSOR;
 begin
  app_apex.setMsgValue(p_result_id, 'Button Templates... ');
 
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Button Templates', p_sort, true, ac_utils.G_TEMPL_BUTTON );
  ac_diff_not_in.app_TEMPL_BUTTON(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_TEMPL_BUTTON(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);

  OPEN v_cur FOR with bt1 as
                (select theme_number, template_name, 
                         template_sr, hot_template_sr, 
                         u_value, u_name
                  from vac_button_templates_4_1 bt
                  where application_id = pi_app_id1
                ),
                 bt2 as
                (select theme_number, template_name, 
                         template_sr, hot_template_sr, 
                         src_view_name, u_value, u_name
                  from vac_button_templates_4_1 bt
                  where application_id = pi_app_id2
                )
                select bt1.theme_number||':'|| bt1.template_name t_name,
                       bt1.u_name          u_name,
                       bt1.u_value         u_value_1,
                       bt2.u_value         u_value_2,
                       bt2.src_view_name   src_view_name,  
                       bt1.template_sr     template_sr_1,  
                       bt2.template_sr     template_sr_2,  
                       bt1.hot_template_sr hot_template_sr_1,
                       bt2.hot_template_sr hot_template_sr_2
                from bt1, bt2
                where bt1.theme_number  = bt2.theme_number
                  and bt1.template_name = bt2.template_name
                  and bt1.u_name        = bt2.u_name
--                  and nvl(bt1.u_value,'*#*')  != nvl(bt2.u_value,'*#*')
                  and ( -- select not equal values. ! here  null = null
                       ( (bt1.u_value is not null or bt2.u_value is not null) and 
                          lnnvl(bt1.u_value=bt2.u_value)
                       )
                      ) 
                order by bt1.theme_number, bt1.template_name; 

  ac_utils.compl_comp(p_result_id, v_parent_id, 1, ac_utils.G_TEMPL_BUTTON, v_cur);     
  
 end app_button_templates;

 procedure app_label_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_cur             SYS_REFCURSOR;
 begin
  app_apex.setMsgValue(p_result_id, 'Label Templates ...');
 
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Label Templates', p_sort, true, ac_utils.G_TEMPL_LABEL );
  ac_diff_not_in.app_templ_label(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_templ_label(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);

  OPEN v_cur FOR with bt1 as
                (select theme_number, template_name, 
                         u_value, u_name
                  from vac_label_templates_4_1 bt
                  where application_id = pi_app_id1
                ),
                 bt2 as
                (select theme_number, template_name, 
                         src_view_name, u_value, u_name
                  from vac_label_templates_4_1 lt
                  where application_id = pi_app_id2
                )
                select bt1.theme_number||':'|| bt1.template_name t_name,
                       bt1.u_name          u_name,
                       bt1.u_value         u_value_1,
                       bt2.u_value         u_value_2,
                       bt2.src_view_name   src_view_name
                from bt1, bt2
                where bt1.theme_number  = bt2.theme_number
                  and bt1.template_name = bt2.template_name
                  and bt1.u_name        = bt2.u_name
--                  and nvl(bt1.u_value,'*#*')  != nvl(bt2.u_value,'*#*')
                  and ( -- select not equal values. ! here  null = null
                       ( (bt1.u_value is not null or bt2.u_value is not null) and 
                          lnnvl(bt1.u_value=bt2.u_value)
                       )
                      ) 
                order by bt1.theme_number, bt1.template_name; 

  ac_utils.compl_comp(p_result_id, v_parent_id, 1, ac_utils.G_TEMPL_LABEL, v_cur);     
  
 end app_label_templates;

 procedure app_page_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_cur             SYS_REFCURSOR;
 begin
  app_apex.setMsgValue(p_result_id, 'Page Templates ...');
   
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Page Templates', p_sort, true, ac_utils.G_TEMPL_PAGE );
  ac_diff_not_in.app_templ_page(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_templ_page(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);

  OPEN v_cur FOR with bt1 as
                (select theme_number, template_name, 
                         u_value, u_name,
                         header_template_sr, 
                         page_body_sr, 
                         footer_template_sr
                  from vac_page_templates_4_1 lt
                  where application_id = pi_app_id1
                ),
                 bt2 as
                (select theme_number, template_name, 
                         src_view_name, u_value, u_name,
                         header_template_sr, 
                         page_body_sr, 
                         footer_template_sr
                  from vac_page_templates_4_1 lt
                  where application_id = pi_app_id2
                )
                select bt1.theme_number||':'|| bt1.template_name t_name,
                       bt1.u_name          u_name,
                       bt1.u_value         u_value_1,
                       bt2.u_value         u_value_2,
                       bt2.src_view_name   src_view_name,
                       bt1.header_template_sr header_template_sr_1, 
                       bt1.page_body_sr       page_body_sr_1, 
                       bt1.footer_template_sr footer_template_sr_1,
                       bt2.header_template_sr header_template_sr_2, 
                       bt2.page_body_sr       page_body_sr_2, 
                       bt2.footer_template_sr footer_template_sr_2
                from bt1, bt2
                where bt1.theme_number  = bt2.theme_number
                  and bt1.template_name = bt2.template_name
                  and bt1.u_name        = bt2.u_name
--                  and nvl(bt1.u_value,'*#*')  != nvl(bt2.u_value,'*#*')
                  and ( -- select not equal values. ! here  null = null
                       ( (bt1.u_value is not null or bt2.u_value is not null) and 
                          lnnvl(bt1.u_value=bt2.u_value)
                       )
                      ) 
                order by bt1.theme_number, bt1.template_name; 

  ac_utils.compl_comp(p_result_id, v_parent_id, 1, ac_utils.G_TEMPL_PAGE, v_cur);     
  
 end app_page_templates;

 procedure app_region_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_cur             SYS_REFCURSOR;
 begin
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'REGION Templates', p_sort, true, ac_utils.G_TEMPL_PAGE );
  ac_diff_not_in.app_templ_region(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_templ_region(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);

  OPEN v_cur FOR with rt1 as
                (select theme_number, template_name, 
                         u_value, u_name,
                         template_sr, 
                         template2_sr, 
                         template3_sr
                  from vac_app_region_template_4_1 rt
                  where application_id = pi_app_id1
                ),
                 rt2 as
                (select theme_number, template_name, 
                         src_view_name, u_value, u_name,
                         template_sr, 
                         template2_sr, 
                         template3_sr
                  from vac_app_region_template_4_1 rt
                  where application_id = pi_app_id2
                )
                select rt1.theme_number||':'|| rt1.template_name t_name,
                       rt1.u_name          u_name,
                       rt1.u_value         u_value_1,
                       rt2.u_value         u_value_2,
                       rt2.src_view_name   src_view_name,
                       rt1.template_sr  template_sr_1, 
                       rt1.template2_sr template2_sr_1, 
                       rt1.template3_sr template2_sr_1,
                       rt2.template_sr  template_sr_2, 
                       rt2.template2_sr template2_sr_2, 
                       rt2.template3_sr template2_sr_2
                from rt1, rt2
                where rt1.theme_number  = rt2.theme_number
                  and rt1.template_name = rt2.template_name
                  and rt1.u_name        = rt2.u_name
                  and ( -- select not equal values. ! here  null = null
                       ( (rt1.u_value is not null or rt2.u_value is not null) and 
                        lnnvl(rt1.u_value=rt2.u_value)
                       )
                      ) 
                 order by rt1.theme_number, rt1.template_name; 

  ac_utils.compl_comp(p_result_id, v_parent_id, 1, ac_utils.G_TEMPL_REGION, v_cur);     
  
 end app_region_templates;

 procedure app_report_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_cur             SYS_REFCURSOR;
 begin
  app_apex.setMsgValue(p_result_id, 'Report Templates');

  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'REPORT Templates', p_sort, true, ac_utils.G_TEMPL_REPORT );
  ac_diff_not_in.app_TEMPL_REPORT (p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_TEMPL_REPORT(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);

  OPEN v_cur FOR with rt1 as
                (select theme_number, template_name, 
                         u_value, u_name,
                         col_template1_sr, 
                         col_template2_sr, 
                         col_template3_sr, 
                         col_template4_sr                  
                         from vac_report_templates_4_1 rt
                  where application_id = pi_app_id1
                ),
                 rt2 as
                (select theme_number, template_name, 
                         src_view_name, u_value, u_name,
                         col_template1_sr, 
                         col_template2_sr, 
                         col_template3_sr, 
                         col_template4_sr                  
                  from vac_report_templates_4_1 rt
                  where application_id = pi_app_id2
                )
                select rt1.theme_number||':'|| rt1.template_name t_name,
                       rt1.u_name          u_name,
                       rt1.u_value         u_value_1,
                       rt2.u_value         u_value_2,
                       rt2.src_view_name   src_view_name,
                       --
                       rt1.col_template1_sr  col_template1_sr_1, 
                       rt1.col_template2_sr  col_template2_sr_1, 
                       rt1.col_template3_sr  col_template3_sr_1,
                       rt1.col_template4_sr  col_template4_sr_1,
                       --
                       rt2.col_template1_sr  col_template1_sr_2, 
                       rt2.col_template2_sr  col_template2_sr_2, 
                       rt2.col_template3_sr  col_template3_sr_2,
                       rt2.col_template4_sr  col_template4_sr_2
                from rt1, rt2
                where rt1.theme_number  = rt2.theme_number
                  and rt1.template_name = rt2.template_name
                  and rt1.u_name        = rt2.u_name
                  and ( -- select not equal values. ! here  null = null
                       ( (rt1.u_value is not null or rt2.u_value is not null) and 
                        lnnvl(rt1.u_value=rt2.u_value)
                       )
                      ) 
                 order by rt1.theme_number, rt1.template_name; 

  ac_utils.compl_comp(p_result_id, v_parent_id, 1, ac_utils.G_TEMPL_REGION, v_cur);     
  
 end app_report_templates;

 procedure app_list_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_cur             SYS_REFCURSOR;
 begin
  app_apex.setMsgValue(p_result_id, 'List Templates ...');
 
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'List Templates', p_sort, true, ac_utils.G_TEMPL_LIST );
  ac_diff_not_in.app_templ_list(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_templ_list(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);

  OPEN v_cur FOR with bt1 as
                (select theme_number, template_name, 
                         u_value, u_name,
                         list_template_current_sr, 
                         list_template_noncurrent_sr, 
                         sub_list_item_current_sr, 
                         sub_list_item_noncurrent_sr, 
                         sub_template_nc_w_child_sr,
                         sub_template_curr_w_child_sr
                  from vac_list_templates_4_1 lt
                  where application_id = pi_app_id1
                ),
                 bt2 as
                (select theme_number, template_name, 
                         src_view_name, u_value, u_name,
                         list_template_current_sr, 
                         list_template_noncurrent_sr, 
                         sub_list_item_current_sr, 
                         sub_list_item_noncurrent_sr, 
                         sub_template_nc_w_child_sr,
                         sub_template_curr_w_child_sr
                  from vac_list_templates_4_1 lt
                  where application_id = pi_app_id2
                )
                select bt1.theme_number||':'|| bt1.template_name t_name,
                       bt1.u_name          u_name,
                       bt1.u_value         u_value_1,
                       bt2.u_value         u_value_2,
                       bt2.src_view_name   src_view_name,
                       bt1.list_template_current_sr list_template_current_sr_1, 
                       bt1.list_template_noncurrent_sr list_template_noncurrent_sr_1, 
                       bt1.sub_list_item_current_sr sub_list_item_current_sr_1, 
                       bt1.sub_list_item_noncurrent_sr sub_list_item_noncurrent_sr_1, 
                       bt1.sub_template_nc_w_child_sr sub_template_nc_w_child_sr,
                       bt1.sub_template_curr_w_child_sr sub_template_curr_w_child_sr,
                       bt2.list_template_current_sr list_template_current_sr_2, 
                       bt2.list_template_noncurrent_sr list_template_noncurrent_sr_2, 
                       bt2.sub_list_item_current_sr sub_list_item_current_sr_2, 
                       bt2.sub_list_item_noncurrent_sr sub_list_item_noncurrent_sr_2, 
                       bt2.sub_template_nc_w_child_sr sub_template_nc_w_child_sr_2,
                       bt2.sub_template_curr_w_child_sr sub_template_curr_w_child_sr_2
                from bt1, bt2
                where bt1.theme_number  = bt2.theme_number
                  and bt1.template_name = bt2.template_name
                  and bt1.u_name        = bt2.u_name
--                  and nvl(bt1.u_value,'*#*')  != nvl(bt2.u_value,'*#*')
                  and ( -- select not equal values. ! here  null = null
                       ( (bt1.u_value is not null or bt2.u_value is not null) and 
                          lnnvl(bt1.u_value=bt2.u_value)
                       )
                      ) 
                order by bt1.theme_number, bt1.template_name; 

  ac_utils.compl_comp(p_result_id, v_parent_id, 1, ac_utils.G_TEMPL_LIST, v_cur);     
  
 end app_list_templates;

 procedure app_popuplov_templates(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_cur             SYS_REFCURSOR;
 begin
  app_apex.setMsgValue(p_result_id, 'Popup Lov Templates');
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Popup Lov Templates', p_sort, true, ac_utils.G_TEMPL_LOV );
  ac_diff_not_in.app_templ_lov(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_templ_lov(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);

  OPEN v_cur FOR with t1 as
    (
      select theme_class, theme_number, 
             u_name, u_value,  src_view_name 
      from vac_lov_templates_4_1 pt1
      where pt1.application_id = pi_app_id1
     ), t2 as
     (
      select theme_class, theme_number, 
             u_name, u_value 
      from vac_lov_templates_4_1 pt1
      where pt1.application_id = pi_app_id2
     )
    select t1.theme_class||':'||t1.theme_number t_name, 
           t1.u_name, 
           t1.u_value u_value_1, 
           t2.u_value u_value_2,
           t1.src_view_name
    from t1, t2
     where t1.theme_number = t2.theme_number
       and t1.theme_class  = t2.theme_class
       and t1.u_name =  t2.u_name
       and ( -- select not equal values. ! here  null = null
            ( (t1.u_value is not null or t2.u_value is not null) and 
              lnnvl(t1.u_value=t2.u_value)
            )
           ) 
        order by t1.theme_class, t1.theme_number
      ;
  
  ac_utils.compl_comp(p_result_id, v_parent_id, 1, ac_utils.G_TEMPL_LOV, v_cur);     
  
 end app_popuplov_templates;

 procedure app_lists_entries(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, 
                             p_list_name varchar2, p_sort number) is
 v_parent_id       number;
 v_parent_id_1     number;
 v_list_name       varchar2(1000) := '*#*#';
 begin
  app_apex.setMsgValue(p_result_id, 'List Entries ...');
   
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'List Entries', p_sort, true, ac_utils.G_APP_LISTS_ENTRIES );
  ac_diff_not_in.APP_LISTS_ENTRIES(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_list_name);
  ac_diff_not_in.APP_LISTS_ENTRIES(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, p_list_name);
  
  for r in ( 
                with l1 as
                (
                 select l1.entry_text , u_name, u_value,
                        l1.src_view_name
                 from  vac_app_lists_entries_4_1 l1
                 where l1.application_id = pi_app_id1
                   and l1.list_name = p_list_name
                ), l2 as
                (
                 select l1.entry_text, u_name, u_value
                 from  vac_app_lists_entries_4_1 l1
                 where l1.application_id = pi_app_id2
                   and l1.list_name = p_list_name
                )
                select 
                 l1.entry_text t_name, 
                 l1.u_name u_name, 
                 l1.u_value u_value_1,
                 l2.u_value u_value_2,
                 l1.src_view_name
                from l1,l2
                where l1.entry_text = l2.entry_text
                  and l1.u_name = l2.u_name
                   and ( -- select not equal values. ! here  null = null
                          ( (l1.u_value is not null or l2.u_value is not null) and 
                            lnnvl(l1.u_value=l2.u_value)
                          )
                       )
                order by  l1.entry_text
            ) loop
       if (r.t_name != v_list_name) then
         v_parent_id_1  := ac_utils.ins_result_msg(p_result_id,  v_parent_id, r.t_name, 1 , true, ac_utils.G_APP_LISTS_ENTRIES );
         v_list_name    := r.t_name;
       end if;

       insert into ac_results
       (result_id,   parent_id,     param_name, param_val_1, param_val_2 , sort,
        help_text, is_branch, is_src)
       values
       (p_result_id, v_parent_id_1, r.U_NAME,  r.U_value_1, r.U_value_2,     1,
        r.src_view_name||'.'||r.U_NAME,'N','N'   );
    end loop;
  
--  ac_utils.compl_comp(p_result_id, v_parent_id, 1, ac_utils.G_APP_LISTS , v_cur);     
  
 end  app_lists_entries;

 procedure app_lists(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_parent_id_1     number;
 v_parent_id_2     number;
 v_list_name       varchar2(1000) := '*#*#';
 begin
  app_apex.setMsgValue(p_result_id, 'Lists ...');
 
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Lists', p_sort, true, ac_utils.G_APP_LISTS );
  ac_diff_not_in.APP_LIST(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.APP_LIST(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);
  
  for r in ( 
                with l1 as
                (
                 select list_name, u_name, u_value,
                        l1.src_view_name
                 from  vac_app_lists_4_1 l1
                 where l1.application_id = pi_app_id1
                ), l2 as
                (
                 select list_name, u_name, u_value
                 from  vac_app_lists_4_1 l1
                 where l1.application_id = pi_app_id2
                )
                select 
                 l1.list_name t_name, 
                 l1.u_name u_name, 
                 l1.u_value u_value_1,
                 l2.u_value u_value_2,
                 l1.src_view_name
                from l1,l2
                where l1.list_name = l2.list_name
                  and l1.u_name = l2.u_name
                   and ( -- select not equal values. ! here  null = null
                          ( (l1.u_value is not null or l2.u_value is not null) and 
                            lnnvl(l1.u_value=l2.u_value)
                          )
                       )
                order by  l1.list_name
            ) loop
       if (r.t_name != v_list_name) then
         v_parent_id_1  := ac_utils.ins_result_msg(p_result_id,  v_parent_id, r.t_name, 1, true, ac_utils.G_APP_LISTS );
         v_list_name    := r.t_name;
         app_lists_entries(p_result_id, v_parent_id_1, pi_app_id1, pi_app_id2, r.t_name, 1);
       end if;

       insert into ac_results
       (result_id,   parent_id,     param_name, param_val_1, param_val_2 , sort,
        help_text, is_branch, is_src)
       values
       (p_result_id, v_parent_id_1, r.U_NAME,  r.U_value_1, r.U_value_2,     2,
        r.src_view_name||'.'||r.U_NAME,'N','N'   )
       returning id into  v_parent_id_2;
        
    end loop;
 end  app_lists;

 procedure app_breadcrumbs(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id        number;
 v_parent_id_1      number;
 v_breadcrumbs_name varchar2(1000) := '*#*#';
 begin
  app_apex.setMsgValue(p_result_id, 'Breadcrumbs... ');
   
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Breadcrumbs', p_sort, true, ac_utils.G_APP_BREADCRUMBS );
  ac_diff_not_in.app_breadcrumbs(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_breadcrumbs(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);
  
  for r in ( 
                with l1 as
                (
                 select breadcrumb_name, u_name, u_value, breadcrumb_id, 
                        src_view_name
                 from  vac_app_breadcrumbs_4_1  
                 where application_id = pi_app_id1
                ), l2 as
                (
                 select breadcrumb_name, u_name, u_value, breadcrumb_id
                 from  vac_app_breadcrumbs_4_1 
                 where application_id = pi_app_id2
                )
                select 
                 l1.breadcrumb_name t_name, 
                 l1.u_name u_name, 
                 l1.u_value u_value_1,
                 l2.u_value u_value_2,
                 l1.src_view_name,
                 l1.breadcrumb_id  breadcrumb_id_1,
                 l2.breadcrumb_id  breadcrumb_id_2
                from l1,l2
                where l1.breadcrumb_name = l2.breadcrumb_name
                  and l1.u_name = l2.u_name
                   and ( -- select not equal values. ! here  null = null
                          ( (l1.u_value is not null or l2.u_value is not null) and 
                            lnnvl(l1.u_value=l2.u_value)
                          )
                       )
                order by  l1.breadcrumb_name
            ) loop
       if (r.t_name != v_breadcrumbs_name) then
         v_parent_id_1      := ac_utils.ins_result_msg(p_result_id,  v_parent_id, r.t_name, 1 , true, AC_UTILS.G_APP_BREADCRUMBS );
         v_breadcrumbs_name := r.t_name;
         app_bc_entries(p_result_id, v_parent_id_1, pi_app_id1, pi_app_id2, 1, r.breadcrumb_id_1, r.breadcrumb_id_2);
       end if;

       insert into ac_results
       (result_id,   parent_id,     param_name, param_val_1, param_val_2 , sort,
        help_text,                      is_branch, is_src)
       values
       (p_result_id, v_parent_id_1, r.U_NAME,  r.U_value_1, r.U_value_2,     1,
        r.src_view_name||'.'||r.U_NAME, 'N',       'N'   )
       ;
       
    end loop;
 end  app_breadcrumbs;

 procedure app_bc_entries(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number,
                          p_breadcrumb_id1 number, p_breadcrumb_id2 number) is
 v_parent_id    number;
 v_bc_cur       ac_utils.SIMP_COMP_CUR_TYP;
 begin
  app_apex.setMsgValue(p_result_id, 'Breadcrumbs Entries... ');
 
  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Breadcrumbs Entries', p_sort, true, ac_utils.G_APP_BC_ENTRIES );
  ac_diff_not_in.app_bc_entries(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_breadcrumb_id1, p_breadcrumb_id2);
  ac_diff_not_in.app_bc_entries(p_result_id, v_parent_id, pi_app_id2, pi_app_id1, p_breadcrumb_id2, p_breadcrumb_id1);

/*
  TYPE SIMP_COMP_REC_TYP IS RECORD (
      object_name   varchar2(4000),
      u_name        varchar2(4000),
      u_value_1     varchar2(4000),
      u_value_2     varchar2(4000),
      src_view_name varchar2(4000)
*/
  
           OPEN v_bc_cur FOR 
              with l1 as
              (
               select defined_for_page, u_name, u_value, src_view_name
               from  vac_app_bc_entries_4_1  b
               where b.application_id = pi_app_id1
                 and b.breadcrumb_id  = p_breadcrumb_id1
              ), l2 as
              (
               select defined_for_page, u_name, u_value
               from  vac_app_bc_entries_4_1 b
               where b.application_id = pi_app_id2
                 and b.breadcrumb_id  = p_breadcrumb_id2
              )
              select 
               l1.defined_for_page t_name, 
               l1.u_name u_name, 
               l1.u_value u_value_1,
               l2.u_value u_value_2,
               l1.src_view_name
              from l1,l2
              where l1.defined_for_page = l2.defined_for_page
                and l1.u_name = l2.u_name
                 and ( -- select not equal values. ! here  null = null
                        ( (l1.u_value is not null or l2.u_value is not null) and 
                          lnnvl(l1.u_value=l2.u_value)
                        )
                     )
              order by  l1.defined_for_page;

           ac_utils.simp_comp(p_result_id, v_parent_id, ac_utils.G_APP_BC_ENTRIES, v_bc_cur);
              
           CLOSE v_bc_cur;

 end  app_bc_entries;

 procedure app_shortcuts(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id       number;
 v_parent_id_1     number;
 v_shortcuts_name  varchar2(100) := '#*#*#';
 v_src_1           clob;
 v_src_2           clob;
 v_is_src          varchar2(1);
 v_value_1         varchar2(4000);
 v_value_2         varchar2(4000);
 v_name            varchar2(4000);
 begin
  app_apex.setMsgValue(p_result_id, 'Shortcuts ...');

  v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Shortcuts' , p_sort, true, ac_utils.G_SHORTCUTS );
  ac_diff_not_in.app_shortcuts(p_result_id, v_parent_id, pi_app_id1, pi_app_id2);
  ac_diff_not_in.app_shortcuts(p_result_id, v_parent_id, pi_app_id2, pi_app_id1);
  
  for r in (with sn1 as
             ( select  shortcut_name, u_value, u_name, 
                       shortcut_sr, src_view_name 
               from vac_app_shortcuts_4_1 s1
               where s1.application_id = pi_app_id1
             ),
             sn2 as
             (
               select  shortcut_name, u_value, u_name,
                       shortcut_sr
               from vac_app_shortcuts_4_1 s2
               where s2.application_id = pi_app_id2
             )
             select sn1.shortcut_name, 
                    sn1.u_value u_value_1, 
                    sn2.u_value u_value_2, 
                    sn1.u_name, src_view_name,
                    sn1.shortcut_sr shortcut_sr_1, 
                    sn2.shortcut_sr shortcut_sr_2 
             from sn1, sn2
             where sn1.shortcut_name = sn2.shortcut_name
               and sn1.u_name  =  sn2.u_name
--               and nvl(sn1.u_value,'*#*#*#') != nvl(sn2.u_value,'*#*#*#')
                and ( -- select not equal values. ! here  null = null
                     ( (sn1.u_value is not null or sn2.u_value is not null) and 
                        lnnvl(sn1.u_value=sn2.u_value)
                    )
                   ) 
             order by sn1.shortcut_name
           ) loop
         if (v_shortcuts_name != r.shortcut_name) then
           v_parent_id_1    := ac_utils.ins_result_msg(p_result_id,  v_parent_id, r.shortcut_name, 3, true, ac_utils.G_SHORTCUTS);
           v_shortcuts_name := r.shortcut_name;
         end if;
         if (r.U_NAME = 'SHORTCUT_HS') then
               v_name    := 'SHORTCUT';
               v_src_1   := r.shortcut_sr_1;
               v_src_2   := r.shortcut_sr_2;
               v_value_1 := substr(v_src_1,1,4000);
               v_value_2 := substr(v_src_2,1,4000);
               v_is_src  := 'Y';
         else
               v_name    := r.U_NAME;
               v_src_1   := null;
               v_src_2   := null;
               v_value_1 := r.U_value_1;
               v_value_2 := r.U_value_2;
               v_is_src  := 'N';
         end if;
         
         insert into ac_results
          (result_id,      parent_id,     param_name, param_val_1, param_val_2, sort,
           help_text,      
           src_val_1,     src_val_2,  is_branch, is_src)
         values
          (p_result_id,    v_parent_id_1, v_name,     v_value_1,   v_value_2, 1,
           r.src_view_name||'.'||r.u_name, 
           v_src_1,       v_src_2,    'N',       v_is_src);
  end loop;
 end  app_shortcuts;   

 procedure  app_diff_pages(pi_app_id1 number, pi_app_id2 number, p_res_id number, pi_page_id number) is
 v_res_id     number;
 v_parent_id  number;
 v_date_start timestamp := systimestamp;
 v_interv     interval day to second;
 v_user_msg   varchar2(100) := 'Done...';
 begin

   v_res_id := p_res_id;

   app_apex.setCancel(v_res_id, 'N');
   app_apex.setMsgValue(v_res_id,'Started...');

  -- logger.log(p_text => 'Started...',p_scope => 'ac_diff.app_diff', p_extra => 'P50_DO_CANCEL='|| v('P50_DO_CANCEL'));

   v_parent_id := getRoot(v_res_id);

   app_pages(v_res_id, v_parent_id, pi_app_id1,  pi_app_id2, pi_page_id,   8);

   app_apex.setMsgValue(v_res_id, 'Preparing results...');
   remove_empty_branches(v_res_id);

   v_interv  := systimestamp - v_date_start;

   if app_apex.isCancel(v_res_id) then
     v_user_msg := 'Cancelled by user...';
   else
     v_user_msg := 'Done...';
   end if;
   app_apex.setMsgValue(v_res_id,  v_user_msg || EXTRACT(MINUTE from v_interv)
                                   || 'min '|| EXTRACT(SECOND from v_interv)
                                   || 'sec' );

   -- logger.log(p_text => 'Done...xx',p_scope => 'ac_diff.app_diff', p_extra => 'P50_DO_CANCEL='|| v('P50_DO_CANCEL'));

 end app_diff_pages;

 procedure app_warnings(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 begin
    ac_warnings.app_warnings(p_result_id, pi_parent_id, pi_app_id1, pi_app_id2, p_sort);
 end app_warnings;

end ac_diff;
/

